The result offset and fetch first clauses

<< ColumnPosition | USING clause >>

Derby Reference Manual

Example using a function
You can sort the result set by invoking a function, for example:

SELECT i, len FROM measures
ORDER BY sin(i)

Example specifying null ordering
You can specify the position of NULL values using the null ordering specification:

SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST

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 value of the integer literal (or the 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 value of the literal (or the 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

-- Use of ORDER BY and FETCH FIRST in a subquery
SELECT DISTINCT A.ORIG_AIRPORT, B.FLIGHT_ID FROM
(SELECT FLIGHT_ID, ORIG_AIRPORT
FROM FLIGHTS
ORDER BY ORIG_AIRPORT DESC
FETCH FIRST 40 ROWS ONLY)
AS A, FLIGHTAVAILABILITY AS B
WHERE A.FLIGHT_ID = B.FLIGHT_ID

JDBC (using a dynamic parameter):
PreparedStatement p =
con.prepareStatement("SELECT * FROM T ORDER BY I OFFSET ? ROWS");