.-,------------------------------. 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).
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.
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.
ORDER OF is not allowed if the query specifies:
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.