RESULT OFFSET and FETCH FIRST

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, 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. Use of this clause can increase efficienty and make programming simpler.

Syntax

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

integer-literal

An integer value that specifies the number of rows to skip. The default value is 0.

If non-zero, this must be a positive integer value. If you specify a value greater than the number of rows in the underlying result set, no rows are returned.

 

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

integer-literal

An integer value that specifies the maximum number of rows to return in the result set. The default value is 1.

This must be a positive integer value greater than or equal to 1.

Usage

Note that:

  • ROW and ROWS are synonymous
  • FIRST and NEXT are synonymous

Be sure to specify the ORDER BY clause if you expect to retrieve a sorted result set.

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");
   p.setInt(1, 100);
ResultSet rs = p.executeQuery();

See Also