FETCH FIRST Clause and OFFSET Clause
The OFFSET clause and FETCH FIRST clause are used to return a subset of rows from a result set.
A query can use any combination of the ORDER BY, OFFSET, and FETCH FIRST clauses, but in that order only.
The OFFSET and FETCH FIRST clauses can be used only once per query, and cannot be used in unions or view definitions. They cannot be used in subselects, except a subselect in a CREATE TABLE statement or an INSERT statement.
The FETCH FIRST clause cannot be used in the same SELECT statement as SELECT FIRST rowcount.
The OFFSET clause syntax is as follows:
OFFSET n
where n is a positive integer, a host variable, or a procedure parameter or local variable.
For example, the following query returns rows starting from the 25th row of the result set:
SELECT * FROM MYTABLE ORDER BY COL1 OFFSET 25
The FETCH FIRST clause syntax is as follows:
FETCH FIRST n ROWS ONLY
where n is a positive integer, a host variable, or procedure parameter or local variable.
For example, the following query fetches only the first 10 rows of the result set:
SELECT * FROM MYTABLE ORDER BY COL1 FETCH FIRST 10 ROWS ONLY
In the FETCH FIRST clause, the keywords FIRST and NEXT, and the keywords ROWS and ROW are interchangeable. Because you can offset and fetch first in the same query, NEXT is an alternative for readability. For example:
OFFSET 10 FETCH NEXT 25 ROWS ONLY