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:

1. You can use TOP to return a specified number of rows.
2. You can use OFFSET and FETCH.

Need to Learn More About Sorting? Then read our article Use SQL ORDER BY to Sort Results

OFFSET

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.

Still confused? Then check out this video I’ve put together on SQL OFFSET and FETCH.

FETCH

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 11^th through 15^th 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

Paging

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 3^rd 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:

1. Select the employee information
2. Order the information by HireDate
3. Skip 20 rows and start display results from the 21^st
4. 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.

Boundary Cases

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.