FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Examples of ROWS

The following examples rewrite the examples used in the section about FIRST and SKIP, 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
ROWS 1 TO 10;

or its equivalent

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

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

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 11 TO (SELECT COUNT(*) FROM People);

And this query will return the last 10 records (pay attention to the parentheses):

SELECT id, name
FROM People
ORDER BY name ASC
ROWS (SELECT COUNT(*) - 9 FROM People)
TO (SELECT COUNT(*) FROM People);

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

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 81 TO 100;
Note

ROWS can also be used with the UPDATE and DELETE statements.
See also

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

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.