Getting rows within a specified range for pagination

Question

I am trying to write a query to implement pagination, my basic requirements is that I need a query where I can give min and max range of rows to return for e.g. for page 1 I need record from 1 – 10 for page to 11-20 and so on and so forth.

Through some help form internet and here at SO I have written down the following query but it’s not really working out that way it should and returning me a big sum of rows whatever the range is (probably I am missing some join in the query)

SELECT b.id,b.title,b.name
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY (select NULL as noorder)) AS RowNum, * 
    FROM [student] b
) as alias,[student] b,[class] c
WHERE b.[status]=1 
AND c.id=b.class 
AND c.name='Science' 
AND RowNum BETWEEN 1 AND 5
ORDER BY b.dtetme DESC

I am lost while fixing in it, can someone please point out the mistake. Thank you!

Please check the answer in this question. http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server — Devasayal, Jun 27 '13 at 12:46

score 1 · Accepted Answer · answered Jun 27 '13 at 12:48

1

Your whole query logic + ROW_NUMBER should go in the sub-query. You use outer WHERE just for paging.

ROW_NUMBER must have ORDER BY on which paging is to be implemented.

SELECT  a.id ,
        a.title ,
        a.name
FROM    
   ( 
        SELECT ROW_NUMBER() OVER (ORDER BY b.dtetme DESC) AS RowNum, b.*
        FROM  [student] b 
        INNER JOIN [class] c ON  c.id = b.class
        WHERE b.[status] = 1
        AND c.name = 'Science'
    ) a
WHERE RowNum BETWEEN 1 AND 10 -- change numbers here for pages
ORDER BY t.RowNum

answered Jun 27 '13 at 12:48

Nenad Zivkovic

18,221
6
42
55

it says `The column 'id' was specified multiple times for 'a'.` – Maven Jun 27 '13 at 12:52
@Maven Replace * in subquery with columns you actually need. Check the edited version. – Nenad Zivkovic Jun 27 '13 at 12:52
@Maven And error was because you have `id` column in both `Student` and `Class` tables. – Nenad Zivkovic Jun 27 '13 at 12:54

score 1 · Answer 2 · answered Jun 27 '13 at 12:48

I think the problem is with th addition of [student] b in the FROM, try moving the join into the subquery.

SELECT a.id, a.title, a.name
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY (select NULL as noorder)) AS RowNum, * 
    FROM [student] b
    JOIN [class] c ON c.id = b.class
    WHERE b.[status]=1 
    AND c.name='Science' 
) as a
WHERE a.RowNum BETWEEN 1 AND 5
ORDER BY a.dtetme DESC

Also you may want to consider wrapping this in a procedure or function so you can change the range.

score 0 · Answer 3 · answered Jun 27 '13 at 12:50

It seems you want something like this:

SELECT t.id,t.title,t.name FROM (
    SELECT s.id,s.title,s.name, RowNum = ROW_NUMBER() OVER(ORDER BY s.dtetme DESC)
    FROM student s
    INNER JOIN class c ON c.id = s.class
    WHERE s.[status]=1 AND c.name='Science' ) AS t
WHERE t.RowNum BETWEEN 1 AND 5
ORDER BY t.RowNum

Getting rows within a specified range for pagination

3 Answers3