SQL Reference

Chapter 2 | Supported standards 13
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 format
The OFFSET format is:
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 format
The FETCH FIRST format is:
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
For example, to return information from the twenty-sixth row of the result set sorted by last_name
then by first_name, use the following SELECT statement:
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name,
first_name OFFSET 25 ROWS
To specify that you want to return only ten rows:
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name,
first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY