how to use rownumber to write a paging query in SQL

Question

I know how to use the "select top" to write a paging query, but I don't know how to write it using rownumber. Who can tell me? Thank you!

It was the first result of 'sql server rownumber paging'. You're welcome. — Hogan, Jun 30 '13 at 15:34
Check out my answers here: http://stackoverflow.com/questions/17259716/selecting-specific-row-number-in-sql/17259963#17259963 — Fabian Bigler, Jun 30 '13 at 15:38

score 5 · Accepted Answer · answered Jun 30 '13 at 15:34

5

select * from (
    select ROW_NUMBER() over (order by SalesOrderDate desc) as rn, *
    from sales.salesorderheader
) as x
where rn between @start and @start + @page_size

That ought to do the trick.

answered Jun 30 '13 at 15:34

but he wants to pass the order by as a parameter – Hogan Jun 30 '13 at 15:36
3

@Hogan - How do you discern that? Can't see any mention of that in the question body or title. – Martin Smith Jun 30 '13 at 15:38
Wat? Not sure how dynamic order by made it into " know how to use the "select top" to write a paging query, but I don't know how to write it using rownumber. Who can tell me? Thank you!" – Jun 30 '13 at 15:38
@MartinSmith - My point is that is always the next question in this case, and this topic (and all the variations) has been covered on SO for years. This is a duplicate. – Hogan Jun 30 '13 at 15:39
@MartinSmith - in fact you have a much better answer right here http://stackoverflow.com/questions/5620758/t-sql-skip-take-stored-procedure -- I upvoted it... it was really good. – Hogan Jun 30 '13 at 15:41
1

In fact, I want to test whether I can express my question well in English. My native language isn't English. This is my fisrt time here -- a good place to solve coding problems. I'll visit here often. Thank you all. – invictus Jun 30 '13 at 15:55
1

Careful, between will include extremes so it will return $page_size + 1 records instead of $page_size.Only when start = 0 will return $page_size records. So it's better if where condition is: `where rn > $start and rn <= ($start + $page_size)`. – Lotzy Apr 20 '15 at 16:02

Hiren Dhaduk · Answer 2 · 2013-06-30T15:57:06.797

-3

Try following generalize query :

DECLARE @Start INT = 0
DECLARE @page_size INT = 10
DECLARE @Orderby VARCHAR(max) = 'SalesOrderDate'
DECLARE @Order VARCHAR(max) = 'desc';

WITH temp
     AS (SELECT case when 'SalesOrderDate' then ROW_NUMBER() OVER (ORDER BY SalesOrderDate) 
                                            ELSE ROW_NUMBER() OVER (ORDER BY id) END AS rn,
                *
         FROM   sales.salesorderheader)
SELECT *
FROM   (SELECT row_number() OVER(ORDER BY CASE 
                                          WHEN @Order = 'asc' 
                                          THEN rn 
                                          ELSE -rn 
                                          END) AS finalorder,
               *
        FROM   temp) AS x
WHERE  finalorder BETWEEN @start AND @start + @page_size

edited Jun 30 '13 at 15:57

answered Jun 30 '13 at 15:43

Hiren Dhaduk

2,760
2
19
21

4

This answer is horrible. – Hogan Jun 30 '13 at 15:43
1

`SalesOrderDate` and `id` are highly unlikely to be the same datatype. If `Date` and `int` this won't work (and it won't be able to use any indexes to avoid a sort) – Martin Smith Jun 30 '13 at 15:43
@Hiren Dhaduk -- as Martin says the best way is an IF statement – Hogan Jun 30 '13 at 15:49
no , I found more generalize solution. let me modify my answer . – Hiren Dhaduk Jun 30 '13 at 15:55
Check it we used this type of query in one of our project . it will work properly. – Hiren Dhaduk Jun 30 '13 at 15:58
@martin check my ans i solved that data type mismatch problem . – Hiren Dhaduk Jun 30 '13 at 16:01

how to use rownumber to write a paging query in SQL

2 Answers2