SQL Reference

Chapter 2 | Supported standards 14
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 a
llows 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.
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.
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).
SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name
OFFSET 25 ROWS FETCH FIRST 10 ROWS WITH TIES
FOR UPDATE clause
The FOR UPDATE clause locks records for Positioned Updates or Positioned Deletes via SQL
cursors. The format is:
FOR UPDATE [OF column_expressions]
column_expressions is a list of field names in the database table that you intend to update,
separated by a comma. column_expressions is optional, and is ignored.
Example
Return all records in the employee database that have a SALARY field value of more than
$20,000.
SELECT * FROM emp WHERE salary > 20000
FOR UPDATE OF last_name, first_name, salary
When each record is fetched, it is locked. If the record is updated or deleted, the lock is held until
you commit the change. Otherwise, the lock is released when you fetch the next record.