Be Proactive

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

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

sort-key-expression

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

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.