order-by-clause

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

order-by-clause

Read syntax diagram
               .-,--------------------------.     
               V                 .-ASC--.   |     
>>-ORDER BY--+-----| sort-key |--+------+---+-+----------------><
             |                   '-DESC-'     |   
             +-INPUT SEQUENCE-----------------+   
             '-ORDER OF--table-designator-----'   

sort-key:

Read syntax diagram
>>-+-column-name---------+-------------------------------------><
   +-integer-------------+   
   '-sort-key-expression-'   

A subselect that contains an ORDER BY clause cannot be specified in the outermost fullselect of a view

If the subselect is not enclosed within parentheses and is not the outermost fullselect, the ORDER BY clause cannot be specified. The ORDER BY clause cannot be used in an outermost fullselect that contains a FOR UPDATE clause.

An ORDER BY clause that is specified in a subselect only affects the order of the rows that are returned by the query if the subselect is the outermost fullselect, except when a nested subselect includes an ORDER BY clause and the outermost fullselect specifies that the ordering of the rows should be retained (by using the ORDER OF table-designator clause).

Multiple ORDER BY clauses can be specified in the same subselect if each clause is separated with parentheses.

INPUT SEQUENCE
Indicates that the result table reflects the input order of the rows specified in the VALUES clause of an INSERT statement. INPUT SEQUENCE ordering can be specified only when an INSERT statement is specified in a from-clause.
ORDER OF table-designator
Specifies that the same ordering of the rows for the result table that is designated by table-designator should be applied to the result table of the subselect (or fullselect) that contains the ORDER OF specification. There must be a table reference in the FROM clause of the subselect (or fullselect) that specifies this clause and matches table-designator.

Start of changeFor an ORDER BY clause in an OLAP specification, table-designator must not specify a table function, a materialized view, a nested table expression that is materialized, an alias, or a synonym.End of change

sort-key
A column-name, integer, or sort-key-expression that specifies the value that is to be used to order the rows of the result of the subselect.

If a single sort-key is identified, the rows are ordered by the values of that sort-key. If more than one sort-key is identified, the rows are ordered by the values of the first sort-key, then by the values of the second sort-key, and so on. A sort-key cannot be a LOB or XML expression.

The result table can be ordered by a named column in the select list by specifying a sort-key that is an integer or the column name. The result table can be ordered by an unnamed column in the select list by specifying a sort-key that is an integer or, in some cases, by a sort-key-expression that matches the expression in the select list.

column-name
An identifier that usually identifies a column of the result table. In this case, column-name must be the name of a named column in the select list. If the fullselect includes a set operator, the column name cannot be qualified.

If the query is a subselect, the column-name can also identify a column name of a table, view, or nested table expression identified in the FROM clause, including a column that is defined as implicitly hidden. The subselect must not include any of the following:

  • DISTINCT in the select list
  • Aggregate functions in the select list
  • GROUP BY clause
integer
Start of changeAn unsigned integer that must be greater than 0 and not greater than the number of columns in the result table. The integer n identifies the nth column of the result table.End of change
sort-key-expression
Start of changeAn expression that is not simply a column-name or unsigned integer constant. The query to which ordering is applied must be a subselect to use this form of the sort-key.End of change

The sort-key-expression cannot include an expression that is not deterministic or a function that is defined to have an external action except for the RID built-in function and the ROW CHANGE expression. Any column name in the expression must conform to the rules described Column names in sort keys. If sort-key-expression includes an aggregate function, the input arguments to that function must not reference a named column in the select list that is derived from an aggregate function. An expression cannot be specified if DISTINCT is used in the select list of the subselect.

If the subselect is grouped, the sort-key-expression might or might not be in the select list of the subselect. When sort-key-expression is not in the select list the following rules apply:

  • Each expression in the ORDER BY clause must either:
    • Use one or more grouping expressions
    • Use a column name that either unambiguously identifies a grouping column of R or is specified within a aggregate function.
  • Each expression in the ORDER BY clause must not contain a scalar fullselect.
ASC
Uses the values of the sort-key in ascending order.

ASC is the default.

DESC
Uses the values of the sort-key in descending order.

Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.

Start of changeColumn access controls do not effect the operation of the ORDER BY clause. The order is based on the original column values. However, after column masks are applied, the masked values in the final result table might not reflect the order of the original column values.End of change

Column names in sort keys: A column name in a sort-key must conform to the following rules:

  • If the column name is qualified, the query must be a subselect. The column name must unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect; its value is used to compute the value of the sort specification.
  • If the column name is unqualified and 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 expression in the FROM clause of the ordering subselect.
    • If the column name is identical is one column of the result table, its value is used to compute the value of the sort specification.
    • If the column name is not identical to a column in the result table, it must unambiguously identify a column of a table, view, or nested table expression in the FROM clause of the subselect. If the column name is identical to one column of a table, view, or nested table expression in the FROM clause of the subselect, its value is used to compute the value of the sort specification.