CODEWITHSUNDEEP
sqlsql-serverpagination
Andre Pena
Andre Pena

Reputation: 59336

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

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?

Upvotes: 2

Views: 3814

Answers (1)

Mark Byers
Mark Byers

Reputation: 838276

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

Upvotes: 4

Related Questions