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: enter image description here

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

Can someone explain why so?

You need a sort on your most outer query

Limey
– Limey

2012-06-11 13:20:44 +00:00
Commented Jun 11, 2012 at 13:20 — Limey
– Limey, Commented Jun 11, 2012 at 13:20

aF. · Accepted Answer · 2012-06-11 13:22:22Z

9

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

answered Jun 11, 2012 at 13:22

aF.

67k45 gold badges141 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MartW · Accepted Answer · 2012-06-11 13:22:10Z

8

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.

answered Jun 11, 2012 at 13:22

MartW

12.6k3 gold badges46 silver badges68 bronze badges

Comments

Adam Hughes · Accepted Answer · 2012-06-11 13:26:38Z

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]

answered Jun 11, 2012 at 13:26

Adam Hughes

3,81226 silver badges25 bronze badges

Comments

saluce · Accepted Answer · 2012-06-11 13:22:44Z

2

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

answered Jun 11, 2012 at 13:22

saluce

13.4k3 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQL ROW_NUMBER and sorting issue

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related