Pagination in SQL Server

Question

How do i limit the result of a query (in my case about 60K rows) and select only from the X row to the Y row?

If I use ROW_NUMBER() I don't like my query because it involves 2 select queries .. one to return the rows and one to select the portion I need

Update:

Here's the query I use now:

SELECT  *
FROM    (
        SELECT  row_number() OVER (ORDER BY E.LastChangeDate DESC) AS row, E.*, U.[DisplayName] AS EntryCreatorDisplayName, U.[Email] AS EntryCreatorEmail
        FROM    entries e
        INNER JOIN
                users u
        ON      e.fk_user= u.id
        WHERE   e.EntryRank = 2
                AND u.Administrator = 1
        ) as TableWithRows
WHERE   (row >= 31 AND row <= 60)

MS SQL 2005, and yes I have an identity column because i have an order by over a arbitrart field in my select — Paul, Nov 18 '09 at 17:39

Quassnoi · Answer 1 · 2009-11-18T17:28:07.090

6

WITH    q AS
        (
        SELECT  TOP (@Y) m.*, ROW_NUMBER() OVER (ORDER BY mycol) AS rn
        FROM    mytable m
        ORDER BY
                mycol
        )
SELECT  *
FROM    q
WHERE   rn >= @X

In SQL Server 2000:

SELECT  *
FROM    (
        SELECT  TOP (@Y - @X) *
        FROM    (
                SELECT  TOP (@X) *
                FROM    mytable
                ORDER BY
                        mycol
                ) q
        ORDER BY
                mycol DESC
        ) q2
ORDER BY
        mycol

edited Nov 18 '09 at 17:28

answered Nov 18 '09 at 17:22

Quassnoi

413,100
91
616
614

I believe this is limited to SQL Server 2005 and up – Irwin M. Fletcher Nov 18 '09 at 17:24
@CodeByMoonlight, sorry added that comment before I saw yours – Irwin M. Fletcher Nov 18 '09 at 17:25
I used this method but this involves 2 queryes, one to bring all the rows needed (about 60K) and one to limit them to only 30 (in my case). The first select takes a lot of time to execute and this hurts my overall performance. – Paul Nov 18 '09 at 17:25
`@Paul`: could you please post your query? Probably the query itself needs some improvement. – Quassnoi Nov 18 '09 at 17:27
SELECT * FROM ( SELECT row_number() OVER (ORDER BY E.LastChangeDate DESC) AS row, E.*, U.[DisplayName] AS EntryCreatorDisplayName,U.[Email] AS EntryCreatorEmail from entries e inner join users u on e.fk_user= u.id WHERE e.EntryRank=2 AND u.Administrator = 1 ) as TableWithRows WHERE (row >= 31 AND row <= 60) The query from the entry table returns about 60K rows and takes about 3 seconds. – Paul Nov 18 '09 at 17:32
@Paul: try adding the `TOP` as in my first query. – Quassnoi Nov 18 '09 at 17:34

score 0 · Answer 2 · answered Nov 18 '09 at 17:23

0

Row_Number() function can be used for this. Please refer to following article for usage http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

answered Nov 18 '09 at 17:23

Fahad

1,261
9
19

score 0 · Answer 3 · answered Nov 18 '09 at 17:32

0

Don't have access to SQL at this very moment, but would something like this work?

SELECT tempid=IDENTITY(int, 1, 1), * FROM tbl WHERE tempid >= @x AND tempid <= @y

answered Nov 18 '09 at 17:32

Oliver

566
4
10

this is working only if I insert the select into a temporary table ( it was the known workaround before SQL 2005 rownumber) – Paul Nov 18 '09 at 17:39

score -1 · Answer 4 · answered Nov 18 '09 at 17:19

-1

I'm not too familiar with MSSQL, But in MySQL, I'd do something like this:

LIMIT 50, 10

Where 10 is the number of records to skip, and 50 is the number to take.

answered Nov 18 '09 at 17:19

Ian P

12,840
6
48
70

Pagination in SQL Server

4 Answers4

Linked