OFFSET and FETCH clauses

The OFFSET clause specifies the number of rows to skip, before starting to return rows from the query expression. The FETCH clause specifies the number of rows to return, after processing the OFFSET clause. The OFFSET and FETCH clauses are now supported in subqueries.

Syntax

[ORDER BY {expr|posn}[ ASC | DESC ]
[ , { expr|posn}[ ASC | DESC ] , ...]]
[OFFSET offset_value {ROW | ROWS }
[FETCH {FIRST | NEXT}fetch_value {ROW | ROWS} ONLY ]]

Note: You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

Parameters

Expression of one or more columns of the tables specified in the FROM clause of the SELECT statement.

Integer column position of the columns selected by the SELECT statement.

Indicates whether to order by ascending order (ASC) or descending order. The default is ASC.

Specifies the number of rows to skip, before starting to return the result rows of the query. This value must be a constant: literal or parameter. It does not support expressions or self-contained subqueries. The value must not be negative and the maximum number allowed is 9,223,372,036,854,775,807.

Specifies the number of rows to return, after processing the OFFSET clause. This value must be a constant: literal or parameter. It does not support expressions or self-contained subqueries. The value must not be negative and the maximum number allowed is 9,223,372,036,854,775,807.

Notes

Both the clauses are optional. However, if present, the OFFSET clause must come before the FETCH clause.

If the OFFSET clause is specified without the FETCH clause, the SQL engine skips the specified number of rows and returns all other qualifying rows without an upper limit.

If the FETCH clause is specified without the OFFSET clause, the SQL engine does not skip any rows and the initial value of the OFFSET is 0. This is similar to the TOP clause.

The OFFSET and FETCH clauses need not be preceded by the ORDER BY clause. it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. If the ORDER BY clause is specified, the OFFSET and FETCH clauses must appear right after the ORDER BY clause of a query.

The TOP clause cannot be combined with the OFFSET and FETCH clauses in the same query expression (in the same query scope).

The OFFSET and FETCH clauses cannot be used in the following instances:

Within the CREATE TABLE, CREATE VIEW, UPDATE, DELETE,and INSERT statements.

In queries used with set operators such as UNION, INTERSECT, and MINUS

The OFFSET and FETCH clauses are interpreted as being applied last, after all the other clauses specified in the query are applied.

Example

The following example demonstrates the OFFSET clause in the SELECT statement:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders OFFSET 10;

The above query skips the first 10 rows and returns the rest of the qualified rows.

The following example demonstrates the FETCH clause in the SELECT statement:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
FETCH FIRST 10 ROWS ONLY;

The above query returns the first 10 rows without skipping any

The following example demonstrates both the OFFSET and FETCH clauses in the SELECT statement:

SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;

The above query returns rows 51 through 60 in the result set of the query.

In the following example, the SELECT statement finds find employee details whose salary is less than 5th highest salary:

SELECT * FROM employee WHERE employee.salary <
( SELECT salary from salaries ORDER BY salary desc
OFFSET 5 rows FETCH NEXT 1 ROWS only);