CODEWITHSUNDEEP
sqlsql-serverpagination
user971741
user971741

Reputation:

Getting rows within a specified range for pagination

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!

Upvotes: 1

Views: 918

Answers (3)

Andrey Gordeev
Andrey Gordeev

Reputation: 32499

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

Upvotes: 0

T I
T I

Reputation: 9943

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.

Upvotes: 0

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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

Upvotes: 1

Related Questions