fetch-first-clause

Start of change The fetch-first-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-first-row-count rows, regardless of how many rows there are in the intermediate result table. An attempt to fetch beyond fetch-first-row-count rows is handled the same way as normal end of data. End of change


                     .-1---------------------.             
>>-FETCH -+-FIRST-+--+-----------------------+--+-ROW--+-------->
          '-NEXT--'  '-fetch-first-row-count-'  '-ROWS-'   

>--ONLY--------------------------------------------------------><

fetch-first-row-count: A numeric constant or variable (except not a global variable) that specifies the maximum number of rows to retrieve. The numeric value is cast to BIGINT before processing. The value of fetch-first-row-count must be positive. 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 with a constant for offset-row-count, the database manager would also consider the constant offset value in determining when to cease processing.

Start of change 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. End of change

If both the order-by-clause and fetch-first-clause are specified, the FETCH FIRST operation is always performed on the ordered data.

Notes

Allowed use of variable: fetch-first-row-count can be specified as a variable only as part of the outer fullselect of a DECLARE CURSOR statement or a prepared select-statement.

FIRST and NEXT: The keywords FIRST and NEXT can be used interchangeably. The result is unchanged; however, using the keyword NEXT is generally more readable when using the offset-clause.

Syntax alternatives: The following are supported for compatibility with SQL used by other database products. These alternatives are non-standard. The OFFSET clause and the LIMIT clause can only be specified in the outermost fullselect of a query.

Table 1.
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