order-by-clause

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram             .-,------------------------------.
             V             .-ASC--.           |
>>-ORDER BY----+-sort-key--+------+---------+-+----------------><
               |           '-DESC-'         |
               '-ORDER OF--table-designator-'
 
sort-key:
 
|--+-column-name---------+--------------------------------------|
   +-integer-------------+
   '-sort-key-expression-'
 

The ORDER BY clause specifies an ordering of the rows of the result table. If a single sort specification (one sort-key with associated ascending or descending ordering specification) is identified, the rows are ordered by the values of that 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.

If a sort sequence other than *HEX is in effect when the statement that contains the ORDER BY clause is executed and if the ORDER BY clause involves sort specifications that are SBCS data, mixed data, or Unicode data, the comparison for those sort specifications is done using weighted values. The weighted values are derived by applying the sort sequence to the values of the sort specifications.

A named column in the select list may be identified by a sort-key that is a integer or a column-name. An unnamed column in the select list may be identified by a integer or, in some cases by a sort-key-expression that matches the expression in the select list (see details of sort-key-expression). Names of result columns defines when result columns are unnamed. If the fullselect includes a UNION operator, see fullselect for the rules on named columns in a fullselect.

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 the ORDER BY clause is not specified, the rows of the result table have an arbitrary order.

The number of sort-keys must not exceed 10000-n and the sum of their length attributes must not exceed 10000-n bytes (where n is the number of sort-keys specified that allow nulls).

column-name
Must unambiguously identify a column of the result table. The column must not be a LOB or DATALINK column. The rules for unambiguous column references are the same as in the other clauses of the fullselect. See Column name qualifiers to avoid ambiguity for more information.

If the fullselect includes a UNION, UNION ALL, EXCEPT, or INTERSECT the column name cannot be qualified.

The column-name may also identify a column name of a table, view, or nested-table-expression identified in the FROM clause if the query is a subselect. An error occurs if the subselect includes an aggregation in the select list and the column-name is not a grouping-expression.

integer
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. The identified column must not be a LOB or DATALINK column.
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 contain RRN, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM, and HASHED_VALUE scalar functions if the fullselect includes a UNION, UNION ALL, EXCEPT, or INTERSECT. The result of the sort-key-expression must not be a LOB or DATALINK.

If the subselect is grouped, the sort-key-expression can be an expression in the select list of the subselect or can include a grouping-expression from the GROUP BY clause of the subselect.

ASC
Uses the values of the column in ascending order. This is the default.
DESC
Uses the values of the column in descending order.
ORDER OF table-designator
Specifies that the same ordering used in table-designator should be applied 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 and the table reference must identify a nested-table-expression or common-table-expression. The subselect (or fullselect) corresponding to the specified table-designator must include an ORDER BY clause. The ordering that is applied is the same as if the columns of the ORDER BY clause in the nested-table-expression or common-table-expression were included in the outer subselect (or fullselect), and these columns were specified in place of the ORDER OF clause.

ORDER OF is not allowed if the query specifies: