SQL Server Performance Forum – Threads Archive
Top operator
Hi,
The top operator returns first top rows specified by number. If there are 200 resords in the table,select top 100 * from MyTable will return first 100 records. If I want to return the last 100 records how can I use Top operator to achieve this? Thanks for any helps
Madhivanan
Use ORDER BY with DESC to give you the reverse order. The TOP operator is handled after any ORDER BY statement, so you would effectively get the "BOTTOM 100". Now wouldn’t the BOTTOM operator be a nice extension to T-SQL?
Hi Adriaan,
Thanks for your suggession. It is working well only if the records are ordered by any column. Is there any way to do this without using order by? Yes really it would be helpful if there had been Bottom operator. Madhivanan
Hi Madhivanan, You only know that a given subset of records is the "TOP" or "BOTTOM" of the entire set of records if you order the records to begin with. If you don’t care about the order in which the records appear, then the last 100 records cannot have any specific interest above the first 100 records – logically speaking. Perhaps you are trying to "page" the records to a client application? There’s lots of information on that around here – just not my field of expertise.
In general SQL doesnt store data in any particular order (we can go into clustered indexes another time). Its not a sequential database (ISAM), its relational. This is where its power lies. If you want a concept of first/last/middle, then you need to include somekind of incrimental counter or a datetime on your table. If you do this, you will always be able to do an order by to get the top/bottom of the records. There are some great books/articles on sql internals about why its done this way and how it helps effeciency to not keep track of the order records were added in. I know access has the ability to do it, but remember that access is for smaller databases and is typically slower for database functions than sql is (although back in the days when it was sql 4.2 and access 1.1, access usually won out) Chris
]]>