Search

Monday, September 12, 2011

SQL Server Denali: Select/Skip Top/Bottom N Rows



SQL Server Denali introduces query paging in which you can specify range of rows returned by a SELECT statement. This can be handy when you want to limit number of rows returned by the statement.
This is implemented in ORDER BY clause. two new keywords are added to ORDER BY clause:
1. OFFSET : Skips top N number of rows from the result set
2. FETCH : Fetch next N number of rows from the result set (after skipping rows specified by OFFSET)
1. Return bottom 15 rows only:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode DESC OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY
2. This will remove first 15 rows from the result:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS
3. Remove first 15 rows from the result, and return next 10 rows:
SELECT ProductCode, ProductName, Color FROM ProductMaster ORDER BY ProductCode OFFSET 15 ROWS FETCH NEXT 10 ROWS ONLY


No comments:

Post a Comment