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 –
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.
- SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server
- SQL SERVER – Row Offset in SQL Server For Different Version
- SQL SERVER – MySQL – LIMIT and OFFSET – Skip and Return Only Next Few Rows – Paging Solution
- SQL SERVER – Server-Side Paging in SQL Server – A Better Alternative
- How to do Pagination in SQL Server? – Interview Question of the Week #111
Reference: Pinal Dave (https://blog.sqlauthority.com)