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?

score 10 · Accepted Answer · edited Feb 11 '21 at 16:25

10

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.

edited Feb 11 '21 at 16:25

Stewart

3,935
4
27
36

answered Aug 21 '11 at 11:14

Chris Diver

19,362
4
47
58

Great...i didn't think in this direction. i was trying to select with top 5 then from that i was trying to skip the first with 'skip' word... Thanks a lot... :-) – CoreLean Aug 21 '11 at 11:25
1

+1 for mentioning it's a ranking function - that makes this an even more useful answer – Sascha Aug 21 '11 at 15:34

Sascha · Answer 2 · 2011-08-21T13:33:57.950

5

You could 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 your values.

Update 2:

Corrected error with missing sub query. Thanks to Chris Diver pointing this out.

edited Aug 21 '11 at 13:33

answered Aug 21 '11 at 11:12

Sascha

10,231
4
41
65

ya. i did correct while executing in my sql but forgot to mention it here. :-) – CoreLean Aug 21 '11 at 17:00
I used this inside another subquery and it worked great! The other `;WITH`... solution doesn't work in that case. – travis Sep 23 '16 at 16:03

score 2 · Answer 3 · answered Aug 21 '11 at 11:11

2

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

answered Aug 21 '11 at 11:11

LostInComputer

15,188
4
41
49

score 2 · Answer 4 · edited Feb 11 '21 at 16:27

Something like this:

-- 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;

Skip first row in SQL Server 2005?

4 Answers4

Linked