7.25.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 or ARRAY_AGG set function (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.

Important

• The sort item specification number can only be specified for the sort key in the ORDER BY clause.

• 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.21 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.

You cannot specify array data and structure data for the value expression.

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

When an integer literal is specified for sort key of the sort specification list specified in the following place, it is not considered a sort item specification number but a value expression.

• A WITHIN group specification

• A window order clause

• ARRAY_AGG set function

• 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 null-value sort order specification is omitted, the order of the null values is determined by the combination of the specified value of the adb_sql_prep_null_collation operand and the specified value of the order specification in the server definition or client definition. For details about the adb_sql_prep_null_collation operand, see the topic Operands related to SQL statements (set format) in Detailed descriptions of the server definition operands in Designing the Server Definition in the HADB Setup and Operation Guide.

If the null-value sort order specification is omitted, the null value is ordered as in the following table.

Table 7‒43: Null value order if the null-value sort order specification is omitted

Specified value of adb_sql_prep_null_collation operand	Specified value of order specification	Null value order
HIGH or when omitted	ASC or when omitted	The null value comes last. This is the same action that is taken when NULLS LAST is specified.
	DESC	The null value comes first. This is the same action that is taken when NULLS FIRST is specified.
LOW	ASC or when omitted	The null value comes first. This is the same action that is taken when NULLS FIRST is specified.
	DESC	The null value comes last. This is the same action that is taken when NULLS LAST is specified.

Important

• When searching a viewed table, the order of the null values when the null-value sort order specification is omitted is determined by the specified value of the adb_sql_prep_null_collation operand in the server definition or client definition when the viewed table is searched.

• A null-value sort order specification is not permitted in a sort specification list specified in a WITHIN group specification or ARRAY_AGG set function.

To Page Top