How to properly use the Sql Server ROW_NUMBER function with ordered queries?

Question

I'm trying to understand ROW_NUMBER from MSSQL and making some experiences.

I have these two snippets:

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (order by p.DtDistribuicao) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) as q 

WHERE q.RowNumber BETWEEN 1 AND 20;

and

select top 20 * from dbo.ProcessoInstanciaFonte as p order by p.DtDistribuicao

They both should return the same rows but aren't. What is the problem?

What differences do you see? One difference is that the first query will have one extra column, and you haven't specified an ORDER to return the rows in (e.g. ORDER BY q.RowNumber) so in theory they could be returned in any order. — Mark Byers
– Mark Byers, Commented Oct 6, 2011 at 22:05

Mark Byers · Accepted Answer · 2011-10-06 22:16:42Z

4

I guess that the values of p.DtDistribuicao have some ties. The server is free to pick any of the tied values as the "first" one and the two different queries could give two different results in this case.

You could add a unique field at the end of the ORDER BY as a tie-breaker. For example, these two queries should return the same rows (assuming you have a unique field called Id):

SELECT * 
    FROM 
    (
        SELECT *,
        ROW_NUMBER() OVER (ORDER BY p.DtDistribuicao, p.Id) AS RowNumber
        FROM ProcessoInstanciaFonte as p
    ) AS q     
WHERE q.RowNumber BETWEEN 1 AND 20;
ORDER BY q.RowNumber

SELECT TOP 20 *
FROM dbo.ProcessoInstanciaFonte AS p
ORDER BY p.DtDistribuicao, p.Id

edited Oct 6, 2011 at 22:16

answered Oct 6, 2011 at 22:10

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

3 Comments

Wil Over a year ago

Exactly what I was going to write.

Andre Pena Over a year ago

that makes a lot of sense.. I'll try

Ben Thul Over a year ago

If you want to verify that this is the case, change row_number() to dense_rank(). If the resulting query returns more than 20 rows, you have ties and will see exactly where they are (because the ranking function will return the same for them).

Collectives™ on Stack Overflow

How to properly use the Sql Server ROW_NUMBER function with ordered queries?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related