SQL Reference

order-by-clause

               .-,--------------------.
               V            .-ASC--.  |
>>-ORDER BY-------sort-key--+------+--+------------------------><
                            '-DESC-'
 
sort-key
 
|---+-simple-column-name--+-------------------------------------|
    +-simple-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 direction) is identified, the rows are ordered by the values of that sort 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. The length attribute of each sort-key must not be more than 254 characters for a character column or 127 characters for a graphic column (SQLSTATE 42907).

A named column in the select list may be identified by a sort-key that is a simple-integer or a simple-column-name. An unnamed column in the select list must be identified by an simple-integer or, in some cases, by a sort-key-expression that matches the expression in the select list (see details of sort-key-expression). A column is unnamed if the AS clause is not specified and it is derived from a constant, an expression with operators, or a function. (43)

Ordering is performed in accordance with the comparison rules described in Chapter 3. "Language Elements". The null value is higher than all other values. If the ORDER BY clause does not completely order the rows, rows with duplicate values of all identified columns are displayed in an arbitrary order.

simple-column-name

Usually identifies a column of the result table. In this case, simple-column-name must be the column name of a named column in the select list.

The simple-column-name may also identify a column name of a table, view or nested table identified in the FROM clause if the query is a subselect. An error occurs if the subselect:

• specifies DISTINCT in the select-clause (SQLSTATE 42822)
• produces a grouped result and the simple-column-name is not a grouping-expression (SQLSTATE 42803).

Determining which column is used for ordering the result is described under "Column name in sort keys" (see Notes).

simple-integer

Must be greater than 0 and not greater than the number of columns in the result table (SQLSTATE 42805). The integer n identifies the nth column of the result table.

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 include a correlated scalar-fullselect (SQLSTATE 42703) or a function with an external action (SQLSTATE 42845).

Any column-name within a sort-key-expression must conform to the rules described under "Column names in sort keys" (see Notes).

There are a number of special cases that further restrict the expressions that can be specified.

• DISTINCT is specified in the SELECT clause of the subselect (SQLSTATE 42822).

  The sort-key-expression must match exactly with an expression in the select list of the subselect (scalar-fullselects are never matched).

• The subselect is grouped (SQLSTATE 42803).

  The sort-key-expression can:

  • be an expression in the select list of the subselect,
  • include a grouping-expression from the GROUP BY clause of the subselect
  • include a column function, constant or host 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.

Notes

• Column names in sort keys:

  • The column name is qualified.

    The query must be a subselect (SQLSTATE 42877). The column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the subselect (SQLSTATE 42702). The value of the column is used to compute the value of the sort specification.

  • The column name is unqualified.

    • The query is a subselect.

      If the column name is identical to the name of more than one column of the result table, the column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the ordering subselect (SQLSTATE 42702). If the column name is identical to one column, that column is used to compute the value of the sort specification. If the column name is not identical to a column of the result table, then it must unambiguously identify a column of some table, view or nested table in the FROM clause of the fullselect in the select-statement (SQLSTATE 42702).

    • The query is not a subselect (it includes set operations such as union, except or intersect).

      The column name must not be identical to the name of more than one column of the result table (SQLSTATE 42702). The column name must be identical to exactly one column of the result table (SQLSTATE 42707) and this column is used to compute the value of the sort specification.

    Column Name Qualifiers to Avoid Ambiguity for more information on qualified column names.

• Limits: The use of a sort-key-expression or a simple-column-name where the column is not in the select list may result in the addition of the column or expression to the temporary table used for sorting. This may result in reaching the limit of the number of columns in a table or the limit on the size of a row in a table. Exceeding these limits will result in an error if a temporary table is required to perform the sorting operation.

(43) The rules for determining the name of result columns for a fullselect that involves set operators (UNION, INTERSECT, or EXCEPT) can be found in fullselect.

[ DB2 List of Books | Search the DB2 Books ]