fetch-clause
The fetch-clause sets a maximum number of rows that can be retrieved. 
The fetch-clause sets a maximum number of rows that can be retrieved. It lets the database manager know that the application does not want to retrieve more than fetch-row-count rows, regardless of how many rows there are in the intermediate result table. An attempt to fetch beyond fetch-row-count rows is handled the same way as normal end of data.
A fetch-clause cannot be specified in the outermost fullselect of a view.
Determining a predictable set of rows to retrieve requires the specification of an
ORDER BY clause with sort keys that uniquely identify the sort order of each row in the intermediate
result table. If the intermediate result table includes duplicate sort keys for some rows, the order
of the rows is not deterministic. If there is no ORDER BY clause, the intermediate result table is
not in a deterministic order. If the order of the intermediate result table is not deterministic,
the set of rows retrieved is unpredictable. If both the order-by-clause and
fetch-clause are specified, the fetch-clause is processed on the ordered data.
- fetch-row-count
- An expression that specifies the maximum number of rows to retrieve. fetch-row-count must
not contain a scalar-fullselect, a column reference, a table reference, a user-defined
function reference, or a built-in scalar function identified as restricted in a check constraint;
see check-constraint. If the data type of the expression is not BIGINT, the result
of the expression is cast to a BIGINT value. The value of fetch-row-count must be a positive
number or zero. It cannot be the null value.
Limiting the result table to a specified number of rows can improve performance. In some cases,
the database manager will cease processing the query when it has determined the specified number of
rows. If the offset-clause is also specified, the database manager will also
consider the offset value in determining when to cease processing.
If the fullselect contains an SQL data change statement in the FROM clause, all the rows are modified regardless of the limit on the number of rows to fetch.
Row access controls can indirectly affect
the FETCH FIRST clause because row access controls affect the rows
that are accessible to the authorization ID of the subselect.
Notes
Syntax alternatives:
- The keywords FIRST and NEXT can be used interchangeably. The result is unchanged; however, using the keyword NEXT is more readable when the offset-clause is used.
- The following are supported for compatibility with SQL used by
other database products. These alternatives are non-standard and should
not be used.
Alternative syntax Equivalent syntax LIMIT x FETCH FIRST x ROWS ONLY LIMIT x OFFSET y OFFSET y ROWS FETCH FIRST x ROWS ONLY LIMIT y, x OFFSET y ROWS FETCH FIRST x ROWS ONLY