Fog Creek Software
Discussion Board




SQLServer getting the last record

Anybody know if SQLServer has syntax for the bottom 5 records, such as it has for the Top 5?
When SQLServer does an Insert, does it insert before or after all the records?

SR
Wednesday, August 11, 2004

There is no guarantee on the order of the records unless you specify an order using an index. A clustered index is the only type of index that actually affects the order of the records in the database. Either way you have no control over where a record may end up.

If you are are needing to know the last records added then your table will need a auto incrementing field and you can do a highest to lowest ORDER BY and then use TOP 5 on it.

Justin
Wednesday, August 11, 2004

In other words.

select top 5
from x
order by y desc

Mike
Wednesday, August 11, 2004

Actually,

In other words.

select top 5 *
from x
order by y desc

Mike
Wednesday, August 11, 2004

If I understand you correctly:

You are saying that you can use the 'top 5' command to find the top 5 records.  This implies an ordered set of records.  If you want to find the LAST 5 records,  you can use the 'order by x DESC' to order in the opposite order. 

THEN, the 'top 5' records are what would have been the last 5 records from the earlier query.  Neat.

AllanL5
Wednesday, August 11, 2004

First 5 in the table starting from the top.  Does not imply order at all.  It could a different 5 the next time you query if an insert or update is done between your two queries.

Mike
Wednesday, August 11, 2004

The order that records are stored is not significant, so you should make no assumptions about the order.

Even with a clustered index, the records are only in order on a "macro level", i.e. within a data page, the order is not strict, as there is a mini-index on the page to tell the database which order the rows belong in. DBCC will let you dig around in the actual data if you're curious.

Nemesis
Thursday, August 12, 2004

Have a column in your table indicating what time a row is inserted.


Thursday, August 12, 2004

The definition of SQL requires that there is no absolute order to the rows, i.e. you can't go to "row #5", or to "the last 5 records" in an absolute sense.  It will always require some sort of set definition, either implied or by direct spec in query.

devinmoore.com
Thursday, August 12, 2004

TOP doesn't imply order.

Perhaps it might have you understand it better if you thought of TOP as MAX_NUM_RECORDS_TO_RETRIEVE.

If you think of it that way, you'll understand why BOTTOM doesn't make sense.

Anon
Thursday, August 12, 2004

*  Recent Topics

*  Fog Creek Home