FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Examples of ORDER BY

Sorting the result set in ascending order, ordering by the RDB$CHARACTER_SET_ID and RDB$COLLATION_ID columns of the RDB$COLLATIONS table:

SELECT
  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  RDB$COLLATION_ID AS COLL_ID,
  RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY RDB$CHARACTER_SET_ID, RDB$COLLATION_ID;

The same, but sorting by the column aliases:

SELECT
  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  RDB$COLLATION_ID AS COLL_ID,
  RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY CHARSET_ID, COLL_ID;

Sorting the output data by the column position numbers:

SELECT
  RDB$CHARACTER_SET_ID AS CHARSET_ID,
  RDB$COLLATION_ID AS COLL_ID,
  RDB$COLLATION_NAME AS NAME
FROM RDB$COLLATIONS
ORDER BY 1, 2;

Sorting a SELECT * query by position numbers — possible, but nasty and not recommended:

SELECT *
FROM RDB$COLLATIONS
ORDER BY 3, 2;

Sorting by the second column in the BOOKS table, or — if BOOKS has only one column — the FILMS.DIRECTOR column:

SELECT
    BOOKS.*,
    FILMS.DIRECTOR
FROM BOOKS, FILMS
ORDER BY 2;

Sorting in descending order by the values of column PROCESS_TIME, with NULLs placed at the beginning of the set:

SELECT *
FROM MSG
ORDER BY PROCESS_TIME DESC NULLS FIRST;

Sorting the set obtained by a UNION of two queries.Results are sorted in descending order for the values in the second column, with NULLs at the end of the set;and in ascending order for the values of the first column with NULLs at the beginning.

SELECT
  DOC_NUMBER, DOC_DATE
FROM PAYORDER
UNION ALL
SELECT
  DOC_NUMBER, DOC_DATE
FROM BUDGORDER
ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST;

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.