CODEWITHSUNDEEP
sql-server
DatsunBing
DatsunBing

Reputation: 9076

select bottom rows in natural order

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.

Upvotes: 2

Views: 3857

Answers (4)

Simon Lawrence
Simon Lawrence

Reputation: 11

I was faced with something similar. I have a generated file that contains data I needed to process every other day. So I made a SQL linked-server connection via a text ODBC DSN driver to it. This meant that the above repsonses about setting indicies don't really apply - as the data is just "as is".

As another gotcha - I MUST preserve the ordering of the rows, so if I do a ROW_NUMBER OVER (ORDER BY X PARITION BY Y) it will "distort" the data (ie: change the row sequence).

So what to do?

After a bit of pondering - I wondered if MSSQLSERVER would preserve the ordering of the data if it had the same thing to sort on in every row, so:

with data as (0 as dummy, * from MyTable) 
select row_number() over (order by dummy) as rowID,
myTable.fieldA, myTable.fieldB from myTable

.. and this seems to give a natural-order rownumber, or at least a close approximation to it.

Anyway - hope this helps someone out!

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

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.)

Upvotes: 8

John Pick
John Pick

Reputation: 5650

Kim's original question states that ORDER BY DESC is not possible in this case. However, he stated in a comment that he has a clustered index. Therefore, he can do ORDER BY DESC.

SELECT TOP xxx *
FROM blah
ORDER BY col1, col2, etc DESC

Upvotes: 0

John Sobolewski
John Sobolewski

Reputation: 4572

If you have a "clustered index" on your table the table is in that order. If the table has not clustered index there is no order to the table (it is a heap table).

If it is a heap table I don't know how to get the last x rows... if it has a clustered index you can just order by list the clustered index column(s) and you will get the last x records... whoops... um... assuming you put them in in clustred index order ie autoincrimenting integer... sorry need to think about this more...

see this question and answer

Upvotes: 0

Related Questions