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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
Categories: SQLServer, SQLServer 2012
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 248 other followers

%d bloggers like this: