FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

OFFSET, FETCH

Retrieves a slice of rows from an ordered set

Syntax
SELECT <columns> FROM ...
  [WHERE ...]
  [ORDER BY ...]
  [OFFSET <offset-fetch-expression> { ROW | ROWS }]
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>
Table 1. Arguments for the OFFSET and FETCH Clause
Argument Description

integer-literal

Integer literal

query-parameter

Query parameter place-holder.? in DSQL and :paramname in PSQL

The OFFSET and FETCH clauses are an SQL standard-compliant equivalent for FIRST/SKIP, and an alternative for ROWS.The OFFSET clause specifies the number of rows to skip.The FETCH clause specifies the number of rows to fetch.

When <offset-fetch-expression> is left out of the FETCH clause (e.g. FETCH FIRST ROW ONLY), one row will be fetched.

The choice between ROW or ROWS, or FIRST or NEXT in the clauses is just for aesthetic purposes (e.g. making the query more readable or grammatically correct).There is no difference between OFFSET 10 ROW or OFFSET 10 ROWS, or FETCH NEXT 10 ROWS ONLY or FETCH FIRST 10 ROWS ONLY.

As with SKIP and FIRST, OFFSET and FETCH clauses can be applied independently, in both top-level and nested query expressions.

Note

  1. Firebird doesn’t support the percentage FETCH nor the FETCH …​ WITH TIES defined in the SQL standard.

  2. The FIRST/SKIP and ROWS clause are non-standard alternatives.

  3. The OFFSET and/or FETCH clauses cannot be combined with ROWS or FIRST/SKIP on the same query expression.

  4. Expressions, column references, etc. are not allowed within either clause.

  5. Contrary to the ROWS clause, OFFSET and FETCH are only available on SELECT statements.

Examples of OFFSET and FETCH

Return all rows except the first 10, ordered by column COL1
SELECT *
FROM T1
ORDER BY COL1
OFFSET 10 ROWS
Return the first 10 rows, ordered by column COL1
SELECT *
FROM T1
ORDER BY COL1
FETCH FIRST 10 ROWS ONLY
Using OFFSET and FETCH clauses in a derived table and in the outer query
SELECT *
FROM (
  SELECT *
  FROM T1
  ORDER BY COL1 DESC
  OFFSET 1 ROW
  FETCH NEXT 10 ROWS ONLY
) a
ORDER BY a.COL1
FETCH FIRST ROW ONLY

The following examples rewrite the FIRST/SKIP examples and ROWS examples earlier in this chapter.

Retrieve the first ten names from the output of a sorted query on the PEOPLE table:

SELECT id, name
FROM People
ORDER BY name ASC
FETCH NEXT 10 ROWS ONLY;

Return all records from the PEOPLE table except for the first 10 names:

SELECT id, name
FROM People
ORDER BY name ASC
OFFSET 10 ROWS;

And this query will return the last 10 records.Contrary to FIRST/SKIP and ROWS we cannot use expressions (including sub-queries).To retrieve the last 10 rows, reverse the sort to the first (last) 10 rows, and then sort in the right order.

SELECT id, name
FROM (
  SELECT id, name
  FROM People
  ORDER BY name DESC
  FETCH FIRST 10 ROWS ONLY
) a
ORDER BY name ASC;

This one will return rows 81-100 from the PEOPLE table:

SELECT id, name
FROM People
ORDER BY name ASC
OFFSET 80 ROWS
FETCH NEXT 20 ROWS;
See also

[fblangref50-dml-select-first-skip], [fblangref50-dml-select-rows]