IBM Books

SQL Reference


               V            .-ASC--.  |
>>-ORDER BY-------sort-key--+------+--+------------------------><

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.

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:

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

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.

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.

Uses the values of the column in ascending order. This is the default.

Uses the values of the column in descending order.



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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]