SQL ROW_NUMBER and sorting issue

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?

Answers


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.


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

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

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]


Need Your Help

APC values randomly disappear

php apc

I'm using APC for storing a map of class names to class file paths. I build the map like this in my autoload function:

Pie Chart legend text

asp.net vb.net

I am trying to display the label name and percentage in the legend of pie chart that I have in VB.NET. Below is the code. I cannot find any library reference or code samples to lead me in the ri...