order-by-clause

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 the subselect includes a hierarchical-query-clause, ORDER SIBLINGS BY can be specified. This specifies that the ordering applies to siblings within the hierarchies only and parent rows are sorted before their child rows.

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 3.5 gigabytes.

column-name

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.

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 DATALINK or XML column and must not be the result of the ARRAY_AGG function.

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, 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.

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 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. If the nested-table-expression or common-table-expression has no ORDER BY clause, the order is not defined.

ORDER OF is not allowed if the query specifies:

• a distributed table,
• a table with a read trigger, or
• a logical file built over multiple physical file members.

INPUT SEQUENCE

Specifies that the result table reflects the input order of the rows of an INSERT statement. INPUT SEQUENCE ordering can be specified only when an INSERT statement is specified in a from-clause. If INPUT SEQUENCE is specified and the input data is not ordered, the INPUT SEQUENCE clause is ignored.

Column 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.