sql server - select bottom rows in natural order

Question

How can I select the bottom X rows of a table, based on natural order? I can't do "ORDER BY DESC...", since I'm not ordering it by any column number...

I'm using Sql Server 2008 R2.

Answer 1

SQL doesn't guarantee the order of rows in a table. It only guarantees the order of rows in a query with an explicit ORDER BY. It's not wise to rely on a clustered index, either. A clustered index might be changed or dropped for good reasons, bad reasons, or no reason at all. Also, the query optimizer isn't guaranteed to return rows in the same order as a clustered index. In the absence of an explicit ORDER BY, it's not guaranteed to return rows in the same order from one run to the next. (The optimizer can make different decisions whenever it thinks it should.) Any one of those things can break your code.

Instead, use a query. Sort descending on a timestamp column. (ORDER BY mytimestampcolumn DESC) You can nip the top 'n' rows off that. Since you sort descending, the top rows are the bottom rows. (Couldn't resist.)

Failing a timestamp column, you might try the same with an auto-incrementing id number column, although they're not guaranteed to be in strictly chronological order. (The transaction that gets id number 1000 might commit before the transactions that got numbers 999 and 998.)