The ORDER BY clause specifies an ordering of the rows of the result table.
.-,------------------------------. V .-ASC--. | >>-ORDER BY--+---+-sort-key--+------+---------+-+-+------------>< | | '-DESC-' | | | '-ORDER OF--table-designator-' | '-INPUT SEQUENCE---------------------' sort-key |--+-column-name---------+--------------------------------------| +-integer-------------+ '-sort-key-expression-'
A subselect that contains an ORDER BY clause
cannot be specified in the outermost fullselect of a view.
Note: An ORDER BY clause in a subselect does
not affect the order of the rows returned by a query. An ORDER BY
clause only affects the order of the rows returned if it is specified
in the outermost fullselect.
If the subselect is not enclosed within parentheses and is not the outermost fullselect, the ORDER BY clause cannot be specified.
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 collating 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 collating 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 sum of the length attributes of the sort-keys must not exceed 32766-n bytes (where n is the number of sort-keys specified that allow nulls).
Must unambiguously identify a column of the result
table. The column must not be a DATALINK or XML column and must not
be the result of the ARRAY_AGG function. 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.
This includes columns defined
as implicitly hidden.
An error occurs if the subselect includes
an aggregation in the select list and the column-name is
not a grouping-expression.

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 DATALINK
or XML column and must not be the result of the ARRAY_AGG function.
The sort-key-expression cannot contain RRN, RID,
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 DATALINK or XML.
If the subselect is grouped, the sort-key-expression can be an expression in the select list of the subselect or can include an aggregate function, constant, or variable.
If the nested-table-expression or common-table-expression has
no ORDER BY clause, the order is not defined.
a table with a read trigger, or
Specification of the ORDER BY clause in a select-statement without an UPDATE clause makes the result table read-only. A read-only result table must not be referred to in an UPDATE or DELETE statement.
a table with a read trigger, or