Is there a 'START AT' equivilant in MS-SQL?

Question

Some databases support commands such as:

SELECT TOP 10 START AT 10 * FROM <TABLE>

Essentially I need to pull the first 10 records, then the next 10, then the next 10 etc. Maybe there is another way to do this but in the past I've done it like the above for databases that support 'START AT'.

Answer 1

SELECT Top 10 * 
FROM Table 
WHERE <primary key> Not IN (
    SELECT Top 10 <primaryKey> 
    FROM Table 
    ORDER BY <primary Key> ASC) 
ORDER BY <primary Key> ASC

Answer 2

Which version of SQL Server?

In SQL Server 2000 this is a real pain (though possible using ugly tricks like that posted by stingyjack).

In 2005 and later it's a little easier- look at the Row_Number() function.

And, depending on your client application it may not even be that hard. Some of the ASP.Net grid controls have support for automatic paging.

Answer 3

If you want to be compatible with SQL Server 2000 you could use

SELECT * FROM
(
    SELECT TOP 10 FROM
    (
        SELECT TOP (n * 10) FROM <table> ORDER BY (column) ASC
    ) AS t1 ORDER BY (column) DESC
) AS t2 ORDER BY (column) ASC

In SQL Server 2005 there is a new function Row_Number(). You could use it this way:

WITH Orders AS 
(
     SELECT SalesOrderID, OrderDate, 
     ROW_NUMBER() OVER (order by OrderDate) AS 'RowNumber' 
     FROM SalesOrder
) 
SELECT * 
FROM Orders 
WHERE RowNumber between 10 and 19;