Skip first row in SQL Server 2005?

Question

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

But is there a way to skip the first line from the top result?

I mean that I get the result from select top 5 , then I skip the first line and get only the next 4 lines?

Answer 1

You can use the OVER clause and the ranking function. You cannot filter this directly, so you need a sub-query or a general table expression, the last one is used in the example below.

 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 

Pagination support will be improved in the next version of SQL Server (codename Denali) with the keywords OFFSET and FETCH .

Answer 2

You can do something like this:

 SELECT * FROM ( SELECT row_number() OVER (ORDER BY ID DESC) AS [rownum], * FROM tbl ) T WHERE rownum BETWEEN (2) AND (5) 

Update:

Updated to have its own meaning.

Update 2:

Fixed bug with missing subquery. Thanks to Chris Diver for pointing this out.

Answer 3

What you are looking for is the term paging. For example: http://www.15seconds.com/issue/070628.htm

Answer 4

Something like that:

 -- Test table declare @T table(ID int) -- Add test data insert into @T select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 -- Query using row_number() over(...) -- to get rows 2 - 5 select T.ID from ( select *, row_number() over(order by ID) as rn from @T ) as T where T.rn between 2 and 5