FirebirdSQL logo

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;

FOR UPDATE [OF]

Syntax
SELECT ... FROM single_table
  [WHERE ...]
  FOR UPDATE [OF <column-name-list>]

FOR UPDATE does not do what its name suggests.Its only effect currently is to disable the pre-fetch buffer.

Tip

It is likely to change in a future Firebird version: the plan is to validate cursors marked with FOR UPDATE if they are truly updatable and reject positioned updates and deletes for cursors evaluated as non-updatable.

The OF sub-clause does not do anything at all, and is only provided for syntax compatibility with other database systems.

WITH LOCK

Applies limited pessimistic locking

Syntax
SELECT ... FROM single_table
  [WHERE ...]
  [FOR UPDATE [OF <column_list>]]
  WITH LOCK [SKIP LOCKED]

WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:

  1. extremely small (ideally singleton), and

  2. precisely controlled by the application code.

Caution
This is for experts only!

The need for a pessimistic lock in Firebird is very rare indeed and should be well understood before use of this extension is considered.

It is essential to understand the effects of transaction isolation and other transaction attributes before attempting to implement explicit locking in your application.

If the WITH LOCK clause succeeds, it will secure a lock on the selected rows and prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends.

WITH LOCK can only be used with a top-level, single-table SELECT statement.It is not available:

  • in a subquery specification

  • for joined sets

  • with the DISTINCT operator, a GROUP BY clause or any other aggregating operation

  • with a view

  • with the output of a selectable stored procedure

  • with an external table

  • with a UNION query

As the engine considers, in turn, each record falling under an explicit lock statement, it returns either the record version that is the most currently committed, regardless of database state when the statement was submitted, or an exception.

When the optional SKIP LOCKED clause is specified, records locked by a different transaction are skipped.

Note

If a statement has both SKIP LOCKED and OFFSET/SKIP/ROWS subclauses, locked rows may be skipped before OFFSET/SKIP/ROWS subclause can account for them, thus skipping more rows than specified in OFFSET/SKIP/ROWS.

Wait behaviour and conflict reporting depend on the transaction parameters specified in the TPB block:

Table 1. How TPB settings affect explicit locking
TPB mode Behaviour

isc_tpb_consistency

Explicit locks are overridden by implicit or explicit table-level locks and are ignored.

isc_tpb_concurrency + isc_tpb_nowait

If a record is modified by any transaction that was committed since the transaction attempting to get explicit lock started, or an active transaction has performed a modification of this record, an update conflict exception is raised immediately.

isc_tpb_concurrency + isc_tpb_wait

If the record is modified by any transaction that has committed since the transaction attempting to get explicit lock started, an update conflict exception is raised immediately.

If an active transaction is holding ownership on this record (via explicit locking or by a normal optimistic write-lock) the transaction attempting the explicit lock waits for the outcome of the blocking transaction and, when it finishes, attempts to get the lock on the record again.This means that, if the blocking transaction committed a modified version of this record, an update conflict exception will be raised.

isc_tpb_read_committed + isc_tpb_nowait

If there is an active transaction holding ownership on this record (via explicit locking or normal update), an update conflict exception is raised immediately.

isc_tpb_read_committed + isc_tpb_wait

If there is an active transaction holding ownership on this record (via explicit locking or by a normal optimistic write-lock), the transaction attempting the explicit lock waits for the outcome of blocking transaction and when it finishes, attempts to get the lock on the record again.

Update conflict exceptions can never be raised by an explicit lock statement in this TPB mode.