FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

ROWS

Retrieves a slice of rows from an ordered set

Syntax
SELECT <columns> FROM ...
  [WHERE ...]
  [ORDER BY ...]
  ROWS <value-expression> [TO <value-expression>]
Table 1. Arguments for the ROWS Clause
Argument Description

value-expression

Any integer expressions
Note
ROWS is non-standard syntax

ROWS is a Firebird-specific clause.Use the SQL-standard [fblangref50-dml-select-offsetfetch] syntax wherever possible.

ROWS limits the amount of rows returned by the SELECT statement to a specified number or range.

The ROWS clause also does the same job as the FIRST and SKIP clauses, but neither are SQL-compliant.Unlike FIRST and SKIP, and OFFSET and FETCH, the ROWS and TO clauses accept any type of integer expression as their arguments, without parentheses.Of course, parentheses may still be needed for nested evaluations inside the expression, and a subquery must always be enclosed in parentheses.

Important

  • Numbering of rows in the intermediate set — the overall set cached on disk before the “slice” is extracted — starts at 1.

  • OFFSET/FETCH, FIRST/SKIP, and ROWS can all be used without the ORDER BY clause, although it rarely makes sense to do so — except perhaps when you want to take a quick look at the table data and don’t care that rows will be in a non-deterministic order.For this purpose, a query like “SELECT * FROM TABLE1 ROWS 20” would return the first 20 rows instead of a whole table that might be rather big.
Characteristics of Using ROWS m Without a TO Clause:

Calling ROWS m retrieves the first m records from the set specified.

  • If m is greater than the total number of records in the intermediate data set, the entire set is returned

  • If m = 0, an empty set is returned

  • If m < 0, the SELECT statement call fails with an error

Characteristics of Using ROWS m With a TO Clause:

Calling ROWS m TO n retrieves the rows from the set, starting at row m and ending after row n — the set is inclusive.

  • If m is greater than the total number of rows in the intermediate set and n >= m, an empty set is returned

  • If m is not greater than n and n is greater than the total number of rows in the intermediate set, the result set will be limited to rows starting from m, up to the end of the set

  • If m < 1 and n < 1, the SELECT statement call fails with an error

  • If n = m - 1, an empty set is returned

  • If n < m - 1, the SELECT statement call fails with an error

Not Possible to Use a TO Clause Without a ROWS Clause:

While ROWS is an alternative to the FIRST and SKIP syntax, there is one situation where the ROWS syntax does not provide the same behaviour: specifying SKIP n on its own returns the entire intermediate set, without the first n rows.The ROWS …​ TO syntax needs a little help to achieve this.

With the ROWS syntax, you need a ROWS clause in association with the TO clause and deliberately make the second (n) argument greater than the size of the intermediate data set.This is achieved by creating an expression for n that uses a subquery to retrieve the count of rows in the intermediate set and adds 1 to it, or use a literal with a sufficiently large value.

Replacing of FIRST/SKIP and OFFSET/FETCH

The ROWS clause can be used instead of the SQL-standard OFFSET/FETCH or non-standard FIRST/SKIP clauses, except the case where only OFFSET or SKIP is used, that is when the whole result set is returned except for skipping the specified number of rows from the beginning.

To implement this behaviour using ROWS, you must specify the TO clause with a value larger than the size of the returned result set.