7.24.1 Specification format for the sort specification list

(1) Specification format

sort-specification-list ::= sort-specification[,sort-specification]...
 
  sort-specification ::= sort-key [order-specification] [null-value-sort-order-specification]
    sort-key ::= {value-expression | sort-item-specification-number}
    order-specification ::= {ASC | DESC}
    null-value-sort-order-specification ::= NULLS {FIRST | LAST}

To Page Top

(2) Explanation of specification format

• sort-specification

sort-specification ::= sort-key [order-specification] [null-value-sort-order-specification]

A sort specification specifies a sort key, and optionally an order specification and a specification of the null-value sort order.

The following rules apply:

• No more than 64 sort specifications are permitted in a sort specification list specified in an ORDER BY clause or window order clause.

• No more than one sort specification is permitted in a sort specification list specified in a WITHIN group specification (you cannot specify two or more).

• sort-key

sort-key ::= {value-expression | sort-item-specification-number}

The sort key specifies either a value expression or a sort item specification number.

When an integer literal is specified for sort-key, it is considered a sort item specification number. When a non-integer literal is specified, it is considered a value expression.

value-expression:

Specifies a sort key in the form of a value expression. For details about value expressions, see 7.20 Value expression.

Note that when multiple sort keys are specified, the sort keys that are specified first take the highest priority when sorting. For example, if ORDER BY "C1","C2","C3" is specified, the priority order of the sort keys will be C1, C2, and then C3.

sort-item-specification-number:

Specifies the number of the column that is to be the sort key. For example, if 2 is specified, the sort key will be the second column of the table derived by the query expression body.

Example:

SELECT "C1","C2" FROM "T1"
    ORDER BY 2 ASC

When the SELECT statement above is executed, the sort key will be column C2.

The following rules apply:

• The sort item specification number must be an integer literal.

• The sort item specification number must be a value in the range from 1 to the number of columns in the table derived by the query expression body.

  Example:

  SELECT "C1",SUM("C2"),AVG("C2") FROM "T1"
      GROUP BY "C1" ORDER BY 3 ASC

  When the above SELECT statement is executed, a sort item specification number between 1 and 3 can be specified in the ORDER BY clause.

• When there are two or more sort item specification numbers, the ones specified first take the highest priority when sorting. For example, if ORDER BY 2,3,1 is specified, the priority order for sorting will be column 2, then column 3, and finally column 1.

• You cannot specify a sort item specification number corresponding to [table-specification.]ROW.

  Example of an SQL statement that generates an error:

  SELECT "C1",ROW FROM "T1" ORDER BY 2

Important

A sort item specification number is not permitted in a sort specification list in the following locations:

• A WITHIN group specification

• A window order clause

• order-specification

order-specification ::= {ASC | DESC}

Specifies whether to sort the results in ascending or descending order. Specify either of the following.

ASC: Specify to sort the results in ascending order.

DESC: Specify to sort the results in descending order.

If the order specification is omitted, ASC is assumed.

• null-value-sort-order-specification

null-value-sort-order-specification ::= NULLS {FIRST | LAST}

Specifies the ordering of the null value when sorting. Specify either of the following.

NULLS FIRST: The null value comes first.

NULLS LAST: The null value comes last.

If the specification of the null-value sort order is omitted, the null value is ordered as follows:

• If you specify ASC for order-specification or if you do not specify order-specification, the null value comes last. This is the same action that is taken when NULLS LAST is specified.

• If you specify DESC for order-specification, the null value comes first. This is the same action that is taken when NULLS FIRST is specified.

Important

You cannot specify the null-value sort order in a sort specification list in a WITHIN group specification.

To Page Top