SQL SERVER – Retrieving N Rows After Ordering Query With OFFSET

August 6, 2019

As many of you know my SQL Server Performance Tuning Practical Workshop was sold out for June and July. That was the primary reasons, I was not able to write blog posts based on questions received in an email. Today, we will see the solution to very interesting question received by Martha about Retrieving N Rows After Ordering Query With OFFSET.

Let’s read the email received from Martha –

“Hey Pinal,

I have a table of data and I want to read rows between line number 21 and 30 (including both the values). I know I can use TOP 40 and order the data as per my requirement. Once I do that I can use a ranking function or other temporary table solution to get only rows between 21 and 30.

However, I am looking for a simpler solution where I do not have to write multiple queries or use ranking functions. I want to write also the queries which are efficient in terms of performance.

Would you be able to provide a simple such query where I can just change one or two variables and get rows between some numbers in the table?”

I have often received this question before about retrieving N rows. Here is a simple script which will retrieve rows 10 rows between 21 and 30 based on your ordering column.

I have used the sample database AdventureWorks but you can use any table of your own database.

SELECT *
FROM [AdventureWorks2014].[Person].[Address]
ORDER BY AddressID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Similarly, if you want to retrieve rows from 101 to 105, you can also run the following query.

SELECT *
FROM [AdventureWorks2014].[Person].[Address]
ORDER BY AddressID
OFFSET 100 ROWS FETCH NEXT 5 ROWS ONLY;

SQL SERVER - Retrieving N Rows After Ordering Query With OFFSET offset-fetch

Using offset command is very efficient and I personally use it in many of my own projects. Here are a few additional blog post related to the topic of retrieving N rows.

Reference: Pinal Dave (https://blog.sqlauthority.com)