Skip first row in SQL Server 2005?

Question

We can select Top 10 or Select Top 'N' row from SQL Server.

But is there any way to skip first row from the result of top??

I mean I get result from select top 5, then I skip the first row and get only next 4 rows?

Answer 1

You can use OVER clause and a ranking function. You can't filter on this directly so you need to us a sub query or a common table expression, the example below uses the latter.

DECLARE @MyTable TABLE 
(
    ID INT,
    Name VARCHAR(15)
);
INSERT INTO @MyTable VALUES (1, 'Alice');
INSERT INTO @MyTable VALUES (2, 'Bob');
INSERT INTO @MyTable VALUES (3, 'Chris');
INSERT INTO @MyTable VALUES (4, 'David');
INSERT INTO @MyTable VALUES (5, 'Edgar');

WITH people AS 
(
    SELECT ID, Name, ROW_NUMBER() OVER (ORDER BY ID) RN
    FROM @MyTable
)
SELECT ID, Name
FROM people
WHERE RN > 1;

There will be better support for pagination in the next version of SQL Server (codename Denali) with the OFFSET and FETCH keywords.