SQL ROW_NUMBER and sorting issue

Question

In SQL 2005/2008 database we have table BatchMaster. Columns: RecordId bigint - autoincremental id, BatchNumber bigint - unique non-clustered index, BatchDate). We have sproc that returns paginated data from this table. That sproc works fine for most of the clients, but at one SQL server instance we have problem with records order. In general, at sproc we do

select * from
(
    select row_number() over (order by bm.BatchDate desc, bm.BatchNumber desc) as Row,
    *
    from dbo.BatchMaster bm with (nolock)
)
where Row between @StartingRow and @EndgingRow

So, as you can notice from the script above we want return records sorted by BatchDate and BatchNumber. That's not gonna happen for one of our client:

Records are in wrong order. Also, notice first column (Row), it is not in ascending order.

Can someone explain why so?

Answer 1

Assuming you want the lowest BatchNumber for a given BatchDate with the smallest Row number and that you want orderer by the Row, try this:

select * from
(
    select row_number() over (order by bm.BatchDate desc, bm.BatchNumber asc) as Row,
    *
    from dbo.BatchMaster bm with (nolock)
)
where Row between @StartingRow and @EndgingRow
order by Row

Answer 2

Your code doesn't actually sort the results, it only sets 'Row' based on the order of BatchDate and Batchnumber and appears to be doing that correctly. You need to add ORDER BY Row to your statement.

Answer 3

The ORDER BY clause in your ROW_NUMBER ranking function only applies to calculating the value of that ranking function, it does not actually order the results.

If you would like the records returned in a certain order you will need specify that in your query: ORDER BY [Row]

Answer 4

Change your query to include a sort in the outermost query

select * from
(
    select row_number() over (order by bm.BatchDate desc, bm.BatchNumber desc) as Row,
    *
    from dbo.BatchMaster bm with (nolock)
)
where Row between @StartingRow and @EndgingRow
order by Row