Examples Of

CommentsProcedural SQL (PSQL) 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.

Examples Of

Examples of OFFSET and FETCH

FOR UPDATE [OF]

Examples of `OFFSET` and `FETCH`

`FOR UPDATE [OF]`