Using OFFSET and FETCH with the ORDER BY clause
In this article we explore the OFFSET and FETCH clauses. OFFSET and FETCH are used in conjunction with the SELECT statement ORDER BY clause to provide a means to retrieve a range of records. The starting row to return is determined by the OFFSET value and the maximum number of rows to return from that point on by FETCH.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Using OFFSET and FETCH with the ORDER BY clause
Returning rows from a SQL statement can be an all or nothing affair. In many cases the number of rows returned is very large and this can cause issues if you only need to part of the result set.
When results are sorted using the ORDER BY clause, then some options come into play to limit the number of rows returned:
- You can use TOP to return a specified number of rows.
- You can use OFFSET and FETCH.
In this article we dive into learn more about OFFSET and FETCH. To learn more about TOP, read the article Getting Started with SQL Server: 2. Sort Your Query Results.
The OFFSET argument is used to identify the starting point to return rows from a result. OFFESET is called an argument since it is technically part of the ORDER BY clause. The OFFSET is the number of rows to skip before including them in the result.
The general form for the OFFSET argument is:
SELECT columns FROM table ORDER BY columns OFFSET rows-to-skip ROWS
Where the rows-to-skip is an value greater than or equal to zero.
For example, to show the all but the first 10 employees, ordered by HireDate you could write
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate OFFSET 10 ROWS
Here are some things to consider about OFFSET
- OFFSET is part of the ORDER BY clause. It cannot be used on its own.
- OFFSET values must be zero or greater. A negative number results in an error.
- When OFFSET is 0, then no rows are skipped.
- If OFFSET is greater than the number of rows in the ordered results, then no rows are returned.
The FETCH argument is used to return a set number of rows. FETCH can’t be used by itself, it is used in conjunction with OFFSET.
Continuing with our example, we can show the 11th through 15th employees hired using this statement
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
In the following diagram you can see OFFSET and FETCH at work. OFFSET is being used to skip the first 10 rows and FETCH is then used to display the next 5.
The combination of OFFSET and FETCH make it easy to retrieve a “sliding” window of rows. The start of the window is determined by OFFSET and the height by FETCH.
Uses for OFFSET and FETCH
One of the most popular uses for OFFSET and FETCH is paging. No doubt you have visited website where you see a list of items and at the bottom there is a list of page numbers or a next button.
We use paging all the time on the web. The most popular example I can think of is Google:
The numbers below Google represent page numbers. A portion of the search results are returned with each click.
In similar fashion, suppose we have a web page displaying employees by HireDate. If we wanted to display 20 employees on a page, and we were displaying the 3rd page (employees 21-30), we could use the following query:
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
This query instructs SQL to:
- Select the employee information
- Order the information by HireDate
- Skip 20 rows and start display results from the 21st
- Display the next 10 rows of results.
Getting Top Records
If you wish to get the top ten rows in a query, then you can do so by setting OFFSET to 0. Remember the OFFSET specifies the number of rows to skip. By setting it to zero, we’re telling SQL to start at the first row.
Once OFFSET is set, the next order of business is to set FETCH. Since we’re looking for the top ten, we set FETCH NEXT to 10.
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
If you’re familiar with the TOP clause you may have noticed there are similarities between these two methods. For instance to above example using TOP would look like
SELECT TOP 10 NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate
There are aspects to TOP that don’t apply to OFFSET and FETCH; such as being allowed in statements without an ORDER BY, but as you can see for this example, they return equivalent results.
Getting Bottom Records
To get the bottom records in a result set there are two ways. The first is to order the result in descending order as opposed to ascending. This is places the bottom results to the top. Then you can use fetch as normal.
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
If you don’t wish to alter the order of the results, you can use a sub query to get the record count. This can then be used to set the offset accordingly.
SELECT NationalIDNumber, JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY
There is a certain danger in this method as the the expression to calculate OFFSET may result in a value less than zero. In our example this could happen if the total number of rows was less than ten. To defend against this condition would you want to incorporate a CASE statement into your logic to check against this condition.
Sampling Data in the middle of a result set
One nice feature of FETCH and OFFSET you’re able to write SQL to sample or test data from the middle of result. This is a really handy if you need to see take a peek at the middle of a result set, perhaps one containing millions of rows, without displaying all the rows up to the point you wish to review.
To sample in the middle you would use the same logic as you would for paging. Of course, the number of rows you fetch in this case may be much larger.
Effect of ORDER BY on OFFSET and FETCH
OFFSET and FETCH only work in conjunction with an ORDER BY clause. In effect, SQL first retrieves the data you specified, such as columns, then order the data in ascending or descending order.
Only after this step has completed are rows skipped and the results produced.
Since some values can result in an error, such as a negative OFFSET, let’s explore various combinations of values which may exceed the number of rows in a table to understand which values are safe to use, and which values would throw an SQL error.
We’ll base our examples off of the HumanResources.Employee table, which contains 290 rows.
The only case that results in an error is when the OFFSET is negative. There are cases that don’t return rows, or may return less rows than you think, but those cases don’t throw errors. For the most part, those situations occur when either the OFFSET value is greater than the number of rows in the result (all the rows are skipped), or you are trying to fetch “past” then end of the result.