SQL SERVER – Retrieving N Rows After Ordering Query With OFFSET

4 years ago

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;

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)