Table of contents

Db2 11.5

order-by-clause

The ORDER BY clause specifies an ordering of the rows of the result table.

Read syntax diagramSkip visual syntax diagramORDER BY,sort-keyASCNULLS LASTNULLS FIRSTDESCNULLS FIRSTNULLS LASTORDER OFtable-designatorINPUT SEQUENCE
sort-key
Read syntax diagramSkip visual syntax diagramsimple-column-namesimple-integersort-key-expression

If a single sort specification (one sort-key with associated direction) is identified, the rows are ordered by the values of that sort specification. If more than one sort specification is identified, the rows are ordered by the values of the first identified sort specification, then by the values of the second identified sort specification, and so on. Each sort-key cannot have a data type of CLOB, DBCLOB, BLOB, XML, distinct type on any of these types, or structured type (SQLSTATE 42907).

A named column in the select list can be identified by a sort-key that is a simple-integer or a simple-column-name. An unnamed column in the select list must be identified by an simple-integer or, in some cases, by a sort-key-expression that matches the expression in the select list (see details of sort-key-expression). A column is unnamed if the AS clause is not specified and it is derived from a constant, an expression with operators, or a function.

Ordering is performed in accordance with comparison rules. If an ORDER BY clause contains decimal floating-point columns, and multiple representations of the same number exist in these columns, the ordering of the multiple representations of the same number is unspecified. The null value is higher than all other values. If the ORDER BY clause does not completely order the rows, rows with duplicate values of all identified columns are displayed in an arbitrary order.

simple-column-name
Usually identifies a column of the result table. In this case, simple-column-name must be the column name of a named column in the select list.
The simple-column-name can also identify a column name of a table, view, or nested table identified in the FROM clause if the query is a subselect. This includes columns defined as implicitly hidden. An error occurs in the following situations:
  • If the subselect specifies DISTINCT in the select-clause (SQLSTATE 42822)
  • If the subselect produces a grouped result and the simple-column-name is not a grouping-expression (SQLSTATE 42803)

Determining which column is used for ordering the result is described under Column names in sort keys in the Notes section.

simple-integer
Must be greater than 0 and not greater than the number of columns in the result table (SQLSTATE 42805). The integer n identifies the nth column of the result table.
sort-key-expression
An expression that is not simply a column name or an unsigned integer constant. The query to which ordering is applied must be a subselect to use this form of sort-key. The sort-key-expression cannot include a correlated scalar fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845).

Any column-name within a sort-key-expression must conform to the rules described under Column names in sort keys in the Notes section.

There are a number of special cases that further restrict the expressions that can be specified.
  • DISTINCT is specified in the SELECT clause of the subselect (SQLSTATE 42822).

    The sort-key-expression must match exactly with an expression in the select list of the subselect (scalar-fullselects are never matched).

  • The subselect is grouped (SQLSTATE 42803).
    The sort-key-expression can:
    • be an expression in the select list of the subselect,
    • include a grouping-expression from the GROUP BY clause of the subselect
    • include an aggregate function, constant or host variable.
ASC
Order the rows in ascending order. This is the default.
DESC
Order the rows in descending order.
NULLS FIRST
When ordering rows in ascending or descending order, list null values before all other values.
NULLS LAST
When ordering rows in ascending or descending order, list null values after all other values.
ORDER OF table-designator
Specifies that the same ordering used in table-designator applies to the result table of the subselect. There must be a table reference matching table-designator in the FROM clause of the subselect that specifies this clause (SQLSTATE 42703). The ordering that is applied is the same as if the columns of the ORDER BY clause in the nested subselect (or fullselect) were included in the outer subselect (or fullselect), and these columns were specified in place of the ORDER OF clause.
Note that this form is not allowed in a fullselect (other than the degenerative form of a fullselect). For example, the following is not valid:
(SELECT C1 FROM T1
   ORDER BY C1)
UNION
SELECT C1 FROM T2
   ORDER BY ORDER OF T1
The following example is valid:
SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE
INPUT SEQUENCE
Specifies that, for an INSERT statement, the result table will reflect the input order of ordered data rows. INPUT SEQUENCE ordering can only be specified if an INSERT statement is used in a FROM clause (SQLSTATE 428G4). See table-reference. If INPUT SEQUENCE is specified and the input data is not ordered, the INPUT SEQUENCE clause is ignored.

Notes

  • Column names in sort keys:
    • The column name is qualified.

      The query must be a subselect (SQLSTATE 42877). The column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the subselect (SQLSTATE 42702). The value of the column is used to compute the value of the sort specification.

    • The column name is unqualified.
      • The query is a subselect.

        If the column name is identical to the name of more than one column of the result table, the column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the ordering subselect (SQLSTATE 42702). If the column name is identical to one column, that column is used to compute the value of the sort specification. If the column name is not identical to a column of the result table, then it must unambiguously identify a column of some table, view or nested table in the FROM clause of the fullselect in the select-statement (SQLSTATE 42702).

      • The query is not a subselect (it includes set operations such as union, except or intersect).

        The column name must not be identical to the name of more than one column of the result table (SQLSTATE 42702). The column name must be identical to exactly one column of the result table (SQLSTATE 42707), and this column is used to compute the value of the sort specification.

  • Limits: The use of a sort-key-expression or a simple-column-name where the column is not in the select list might result in the addition of the column or expression to the temporary table used for sorting. This might result in reaching the limit of the number of columns in a table or the limit on the size of a row in a table. Exceeding these limits will result in an error if a temporary table is required to perform the sorting operation.