SKIP and TAKE ‘N’ number of records in a SQL Server Select Query

How do you SKIP and TAKE ‘n’ number of records from a resultset? I have done this previously using LINQ as demonstrated over here Implementing Paging in a Generic List using LINQ

But how do you skip and take ‘n’ number of records in SQL Server 2005/2008? Here’s how:

DECLARE @TT table
(
ProductID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 101, 1, 'AA')
INSERT INTO @TT VALUES ( 203, 1, 'AA');
INSERT INTO @TT VALUES ( 305, 1, 'AA');
INSERT INTO @TT VALUES ( 403, 2, 'BB');
INSERT INTO @TT VALUES ( 553, 2, 'BB');
INSERT INTO @TT VALUES ( 634, 2, 'BB');
INSERT INTO @TT VALUES ( 744, 2, 'BB');
INSERT INTO @TT VALUES ( 838, 3, 'CC');
INSERT INTO @TT VALUES ( 939, 3, 'CC');
INSERT INTO @TT VALUES ( 1245, 3, 'CC');
INSERT INTO @TT VALUES ( 1341, 3, 'CC');
INSERT INTO @TT VALUES ( 1452, 3, 'CC');
INSERT INTO @TT VALUES ( 1565, 4, 'DD');

SELECT ProductID, CategoryGroupID
FROM
(
SELECT tbl.*, ROW_NUMBER() OVER (ORDER BY ProductID) rownum
FROM @TT as tbl
) seq
 WHERE seq.rownum BETWEEN 6 AND 10

As you can see in the query above, we are skipping the first 5 records on an ordered resultset and taking the next 5 records.

OUTPUT

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: