How to return last 10 rows of a table?

Question

Post reply

How to return last 10 rows of a table?

mm.sailaja

Newbie

Points: 8
More actions
October 17, 2010 at 10:11 am

#227980

Hi guys,
How to return last 10 rows of a table??

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7186 More actions · Answer 1

use TOP 10

with an appropriate "order by" to give you the 10 you need.

The tricky bit may be working out what you mean by "last" if you do not have a column that defines that for you.

For reference a table by definition is unordered so you must have something in the data that defines the order to satisfy such a query.

Mike John

Jeffrey Williams SSC Guru Points: 90250 More actions · Answer 2

You need to define what the last 10 is. A table is an unordered set - so, to get a particular order you need to use ORDER BY on your query.

SELECT TOP 10

FROM sometable

ORDER BY somecolumn DESC

This will give you the 'last' 10 based upon that order.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mike John SSCertifiable Points: 7186 More actions · Answer 3

Snap!

It would have been hard to get two answers closer...

Mike

WayneS SSC Guru Points: 95409 More actions · Answer 4

Mike John (10/17/2010)
Snap!
It would have been hard to get two answers closer...
Mike

Nah, I've seen the same answer from different folks come in within the same minute before here.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 5

In the absence of anything in particular to sort on, this may be what you're looking for

;WITH cte1 AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowAsc,

*

FROM YourTable

)

,

cte2 AS

(

--last 10 rows in reverse order

SELECT TOP 10 *

FROM cte1

ORDER BY RowAsc DESC

)

--last 10 rows in original order

SELECT *

FROM cte2

ORDER BY RowAsc

LutzM SSC Guru Points: 107049 More actions · Answer 6

Please any book on RDBMS.

What is the message you're trying to bring across with that statement? Sounds incomplete...

Tables have no order.

The question was not about the order of tables but about the order of records within a table. If you request the OP to be as precise as possible, you should do so as well.

Your question makes no sense.

Neither does your answer (at least the sections I quoted).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Perry Whittle SSC Guru Points: 233841 More actions · Answer 7

the question displays a degree of ambiguity but not stupidity!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

CELKO (10/17/2010)
Please any book on RDBMS. Tables have no order. Your question makes no sense.
Read Codd's Information Principle, so you will see what a stupid question this is. An ordering relationship has to be modeled with a scalar valued column.

Heh... there ya go again being all "friendly" and the like. You're absolutely correct but just not right. 😉 It's not a stupid question if you know what order you want and just forget to say it in such a way to keep OSHA from declaring that you have to install handrails on your hinny so all the relational zealots can jump your butt at once. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mm.sailaja Newbie Points: 8 More actions · Answer 9

Hi guys!!

I know that a table is an unordered. Order by clause gives us the result in a sorted manner. I don't want the result in a sorted order. I have written a simple query using emp table.

select * from (select e.*, rownum l from emp e)

where l > (select max(rownum) - 10 from emp);

Please check it out once.

Perry Whittle SSC Guru Points: 233841 More actions · Answer 10

First identify the column(s) that comtain the pertinent data. Once identified the following will suffice!

Select TOP 10 column, column, ...

From mytable

Order by somecolumn desc

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

mm.sailaja (10/18/2010)
Hi guys!!
I know that a table is an unordered. Order by clause gives us the result in a sorted manner. I don't want the result in a sorted order. I have written a simple query using emp table.
select * from (select e.*, rownum l from emp e)
where l > (select max(rownum) - 10 from emp);
Please check it out once.

What guarantee is there that RowNum has no gaps in it? If you use RowNum in Perry's code, it'll do the trick for you even if it does. Not quite so with your code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233841 More actions · Answer 12

Jeff Moden (10/18/2010)
Not quite so with your code.

Junk the snippy little aggregate function on the rownum with gaps too 🙂

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉