order-by-clause

sort-key

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. Each sort-key cannot have a data type of CLOB, DBCLOB, BLOB, XML, distinct type on any of these types, or structured type (SQLSTATE 42907).

A named column in the select list can 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.

Ordering is performed in accordance with comparison rules. If an ORDER BY clause contains decimal floating-point columns, and multiple representations of the same number exist in these columns, the ordering of the multiple representations of the same number is unspecified. 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 can also identify a column name of a table, view, or nested table identified in the FROM clause if the query is a subselect. This includes columns defined as implicitly hidden. An error occurs in the following situations:

• If the subselect specifies DISTINCT in the select-clause (SQLSTATE 42822)
• If the subselect 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 names in sort keys in the Notes section.

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 in the Notes section.

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 an aggregate function, constant or host variable.

ASC

Order the rows in ascending order. This is the default.

DESC

Order the rows in descending order.

NULLS FIRST

When ordering rows in ascending or descending order, list null values before all other values.

NULLS LAST

When ordering rows in ascending or descending order, list null values after all other values.

ORDER OF table-designator

Specifies that the same ordering used in table-designator applies 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 (SQLSTATE 42703). The ordering that is applied is the same as if the columns of the ORDER BY clause in the nested subselect (or fullselect) were included in the outer subselect (or fullselect), and these columns were specified in place of the ORDER OF clause.

Note that this form is not allowed in a fullselect (other than the degenerative form of a fullselect). For example, the following is not valid:

(SELECT C1 FROM T1
   ORDER BY C1)
UNION
SELECT C1 FROM T2
   ORDER BY ORDER OF T1

The following example is valid:

SELECT C1 FROM
   (SELECT C1 FROM T1
      UNION
    SELECT C1 FROM T2
    ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE

INPUT SEQUENCE

Specifies that, for an INSERT statement, the result table will reflect the input order of ordered data rows. INPUT SEQUENCE ordering can only be specified if an INSERT statement is used in a FROM clause (SQLSTATE 428G4). See table-reference. If INPUT SEQUENCE is specified and the input data is not ordered, the INPUT SEQUENCE clause is ignored.