RETRIEVE THE LAST 5 ROWS FROM A TABLE

Question

3.50/5 (2 votes)

See more:

//HOW CAN I GET THE LAST 5 DATA IN THIS TABLE WITHOUT USING TEMPORARY TABLES?
Table="AA"
Column name="NAME"
a
B
C
D
E
B
C
D
a
B
C
D
a
B
C
D
E

//Output should be
a
B
C
D
E

Posted 17-Feb-12 1:38am

The Doer

Updated 17-Feb-12 1:51am

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 17-Feb-12 7:55am

are there any other columns on this table? Some fields that reference?

4 solutions

Solution 2

If it is SQL 2005 or newer you can use Row_Number() function

SQL

SELECT x.[Name] 
FROM (
SELECT [Name], Row_Number() OVER (ORDER BY < index fields >) AS RowNumber
FROM AA
) x
WHERE x.RowNumber >= ((SELECT COUNT(*) FROM AA) -5)

The Row_Number function needs to have an order by. If you want it the same order as what is on the disk use the clustered index field.

Posted 17-Feb-12 1:51am

ZurdoDev

Comments

The Doer 17-Feb-12 8:01am

HOW TO USE CLUSTURED INDEX HERE??WHEN I DON'T HAVE ANY PRIMARY KEY HERE..

ZurdoDev 17-Feb-12 8:05am

The data IS ordered by SQL even if you don't specify a key. So, I would recommend adding a key but if you can't you have to look at how the data is sorted and then use that.

The Doer 17-Feb-12 8:07am

THEN PLS GET ME THE QUERY TO GET THOSE 5 LAST VALUES...

ZurdoDev 17-Feb-12 8:23am

I am trying to help you so please don't scream at me. YOU have to look at your table to see how it is sorted. I do not know. SQL sorts the data whether you have an index or not.

Solution 1

Do not shout in the title of or body of your question - it is rude and puts people off answering - you are less likely to get an answer therefore.

On to the solution:

Use SELECT TOP and ORDER BY clauses to reverse order your table then select the first 5 rows.

http://www.w3schools.com/sql/sql_top.asp[^]

Hope this helps,
Ed

Posted 17-Feb-12 1:47am

Ed Nutting

Comments

The Doer 17-Feb-12 7:52am

OH OKAY, AM NEW HERE.. HENCE UNAWARE WITH THOSE CONVENTIONS...:(
COMMING TO THE SOLUTION PART:
IF I USE ORDER BY CLAUSE THEN THE ORDE CHANGES AND I WON'T GET THE REQUIRED OUTPUT..

Herman<T>.Instance 17-Feb-12 7:54am

do you know where the CAPSLOCK key is on your keyboard. Please turn if off.

Ed Nutting 17-Feb-12 7:56am

Weel said :)

Herman<T>.Instance 17-Feb-12 7:53am

if you would do an order by, and that can only be on field Name in the above example, you would get 'EEDDD' as anwser

Ed Nutting 17-Feb-12 7:56am

Aah..yes true...sorry forgot about that... Would have expected the OP's table to have some form of index/id field really but I guess that was a bad assumption to make... :s

Edit: Is this actually possible without having an index/using orderby?

The Doer 17-Feb-12 8:03am

I HAVE PUT THE CAPS KEY ON INTENTIONALLY... JUST AS TO BE THE FOCUSED ONE AMONG ALL COMMENTS ;)

The Doer 17-Feb-12 8:04am

I HAVE DONE THAT USING TEMPORARY TABLE...JUST WANT TO KNOW SOME OTHER WAYS..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Accepted Answer · 2012-02-17T14:24:00

Solution 3

For SQL 2005

SQL

SELECT  NAME
FROM    (
        SELECT  ROW_NUMBER() OVER( ORDER BY [AlwaysNull]) AS RowNumber,
                NAME
        FROM    (
                SELECT  [AlwaysNull] = NULL,
                        NAME
                FROM    AA
                ) AS a
        ) b
WHERE   RowNumber>(SELECT COUNT(*) FROM AA)-5

Posted 17-Feb-12 14:24pm

Costica U

Comments

The Doer 17-Feb-12 23:12pm

YEAH HERE I GOT THE REQUIRED OUTPUT..:)
THANK YOU SIR...!

The Doer · Accepted Answer · 2012-02-17T18:54:00

Solution 4

//WE CAN ALSO GET THE SOLUTION BY THIS--

DECLARE @TAB TABLE(ID INT IDENTITY(1,1),SNAME VARCHAR(MAX))
INSERT INTO @TAB(SNAME) SELECT NAME FROM AA
SELECT SNAME FROM @TAB WHERE ID NOT IN
(SELECT TOP(SELECT COUNT(*)-5 FROM @TAB)ID FROM @TAB)

Posted 17-Feb-12 18:54pm

The Doer