Home > SQLServer, SQLServer 2012 > TSQL – Select/Skip Top/Bottom N Rows – “Denali” Way

TSQL – Select/Skip Top/Bottom N Rows – “Denali” Way


“Denali” introduces Ad-hoc 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 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)

Let’s see it in action:

1. This will remove first 5 rows from the result:
— © 2011 – Vishal (http://SqlAndMe.com)
— Remove First 5 Rows from the Result Set
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID
OFFSET 5 ROWS

2. Remove first 5 rows from the result, and return next 10 rows:
— Remove First 5 Rows from the Result Set,
— Return Next 10 Rows
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY

3. Return bottom 5 rows only:
— Return Bottom 5 Rows
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ProductID DESC
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY

For syntax and other options refer BOL : ORDER BY Clause (Transact-SQL)

Hope This Helps!

Vishal

  1. jon
    12.06.2014 04:31 | #1

    Very useful, Thanks

  1. No trackbacks yet.