OFFSET and FETCH FIRST clauses

The OFFSET and FETCH FIRST clauses are used to return a specified range of rows beginning from a particular starting point in a result set. The ability to limit the rows retrieved from large result sets allows you to "page" through the data and improves efficiency.

The OFFSET clause indicates the number of rows to skip before starting to return data. If the OFFSET clause is not used in a SELECT statement, the starting row is 0. The FETCH FIRST clause specifies the number of rows to be returned, either as an unsigned integer greater than or equal to 1 or as a percentage, from the starting point indicated in the OFFSET clause. If both OFFSET and FETCH FIRST are used in a SELECT statement, the OFFSET clause should come first.

The OFFSET and FETCH FIRST clauses are not supported in subqueries.

OFFSET clause format

The OFFSET format is:

Copy

OFFSET [ n {ROWS | ROW} ]

n is an unsigned integer. If n is larger than the number of rows returned in the result set, then nothing is returned and no error message appears.

ROWS is the same as ROW.

FETCH FIRST clause format

The FETCH FIRST format is:

Copy

FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ]

n is the number of rows to be returned. The default value is 1 if n is omitted.

n is an unsigned integer greater than or equal to 1 unless it is followed by PERCENT. If n is followed by PERCENT, the value may be either a positive fractional value or an unsigned integer.

ROWS is the same as ROW.

WITH TIES must be used with the ORDER BY clause.

WITH TIES allows more rows to be returned than specified in the FETCH count value because peer rows, those rows that are not distinct based on the ORDER BY clause, are also returned.

Examples

Return information from the twenty-sixth row of the result set sorted by last_name then by first_name.

Copy

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS

Specify that you want to return only ten rows.

Copy

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

Return the ten rows and their peer rows (rows that are not distinct based on the ORDER BY clause).

Copy

SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS WITH TIES