FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Ordering UNION-ed Sets

Not-parenthesized query expressions contributing to a UNION cannot take an ORDER BY clause.You can order the entire output, using one ORDER BY clause at the end of the overall query, or use parenthesized query expressions, which do allow ORDER BY.

The simplest — and, in some cases, the only — method for specifying the sort order is by the ordinal column position.However, it is also valid to use the column names or aliases, from the first contributing query only.

The ASC/DESC and/or NULLS directives are available for this global set.

If discrete ordering within the contributing set is required, use parenthesized query expressions, derived tables, or common table expressions for those sets.

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;