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?
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.
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.
What you are looking for is the term paging. Like this: http://www.15seconds.com/issue/070628.htm
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;
精彩评论