Last n records from a table

Question

Post reply

Last n records from a table

Viewing 5 posts - 16 through 19 (of 19 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

sunil88_pal88 (2/1/2010)
So why has microsoft has made some opposite of TOP .They should also have made BOTTOM

They actually have. Sort of, at least...

But is doesn't have anything to do with the TOP clause .

It's more the standard setting, usually left out when writing TOP, where Microsoft came up with an opposite: its ASC(ending) and DESC(ending).

And MS did even allow us to use it for other statements than TOP, e.g. ORDER BY 😉

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]

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 2

Sunil,

I have one question. Is "Id" column in the table an IDENTITY column? If that is so, then you can sort on "Id Desc".

If "Id" is not IDENTITY, then you may want to add an IDENTITY column to the table and then you can use that to find the BOTTOM 3 rows. If you add IDENTITY column to the current table, then there is still no guarantee you will get the right results. It may be helpful going forward

As for how TOP works. Create a clustered index on Number on this table and then do a Top 3. Let us know what you find.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

vstitte (2/1/2010)
As for how TOP works. Create a clustered index on Number on this table and then do a Top 3. Let us know what you find.

It may be the 'first' three rows in clustered index order or it may not. Without an Order By there is NO GUARANTEE as to the order that rows are returned or the rows that are selected for the TOP.

Extrapolating general behaviour from a trivial example is a dangerous thing to do. Without an ORDER BY, TOP 3 means ANY 3 rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 4

Gail,

I was just trying to make your exact same point. Assuming that TOP 3 without order by clause will get you the 3 records that were inserted first is a wrong assumption. I wanted Sunil to see the behaviour if you physically reorganize the table and see what happens.

Sorry if I wasnt clear.

Thats all.

GSquared SSC Guru Points: 260824 More actions · Answer 5

sunil88_pal88 (2/1/2010)
So why has microsoft has made some opposite of TOP .They should also have made BOTTOM

They have. Use top, and invert the order.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon