Paging Results in SQL Server 2000

Question

Paging Results in SQL Server 2000

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I sometimes need to query SQL Server 2000 and paginate the results however there is only the TOP clause that limits the number of records returned without an offset. Other banks like MySQL or PostgreSQL have this and query would look like this:

SELECT * FROM tabela LIMIT 0, 50
ou
SELECT * FROM tabela LIMIT 50, OFFSET 0

How can I work around this problem or emulate this functionality in SQL Server 2000.

sql sql-server

asked by anonymous 21.03.2014 / 14:29

2 answers

Error calling method on fragment PHP crypt password

score 2 · Answer 1

First of all I would recommend a nice upgrade, however there is a way to induce you to do something similar

;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

In this SOEN article in a much more complete way , shows other possibilities, in addition to giving a more detailed explanation

score 0 · Answer 2

The query below has the same result as "limit 50, 100", that is to say between 51 and 100.

SELECT TOP 50 *
FROM tabela 
WHERE not ID in ( 
  SELECT TOP 50 ID
  FROM   tabela
  ORDER BY ID ASC
) a 
ORDER BY ID ASC;