The result offset and fetch first clauses

The result offset clause provides a way to skip the N
first rows in a result set before starting to return any
rows. The fetch first clause, which can be combined with
the result offset clause if desired, limits the number of rows
returned in the result set. The fetch first clause can
sometimes be useful for retrieving only a few rows from an
otherwise large result set, usually in combination with an ORDER BY
clause. The use of this clause can give efficiency benefits. In
addition, it can make programming the application simpler.

Syntax

OFFSET { integer-literal | ? } {ROW | ROWS}

FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

ROW is synonymous with ROWS and FIRST is synonymous with NEXT.

For the result offset clause the integer literal (or dynamic
parameter) must be equal to
0 (default if the clause is not given), or positive. If it is larger
than the number of rows in the underlying result set, no rows are
returned.

For the fetch first clause, the literal (or dynamic parameter) must be 1 or
higher. The literal can be omitted, in which case it defaults to 1. If
the clause is omitted entirely, all rows (or those rows remaining if
a result offset clause is also given) will be returned.

Examples

-- Fetch the first row of T
SELECT * FROM T FETCH FIRST ROW ONLY
-- Sort T using column I, then fetch rows 11 through 20 of the sorted
-- rows (inclusive)
SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
-- Skip the first 100 rows of T
-- If the table has fewer than 101 records, an empty result set is
-- returned
SELECT * FROM T OFFSET 100 ROWS
JDBC:
PreparedStatement p =
con.prepareStatement("SELECT * FROM T ORDER BY I OFFSET ? ROWS");
p.setInt(1, 100);
ResultSet rs = p.executeQuery();

Note:
Make sure to specify the ORDER BY clause if you
expect to retrieve a sorted result set.
If you do not use an ORDER BY clause, the result set that is retrieved will
typically have the order in which the records were inserted.