Show TOC

Background documentationORDER Clause (order_clause)

 

The ORDER clause (order_clause) specifies a sort sequence for a result table.

Structure

Syntax Syntax

  1. <order_clause> ::=
  ORDER BY <sort_spec>,...

<sort_spec> ::=
  <unsigned_integer> [ASC | DESC]
| <expression> [ASC | DESC]
End of the code.
Examples

SQL Tutorial, Selecting and Arranging Rows

Explanation

The sort columns specified in the ORDER clause determine the sequence of the sort criteria.

A number n specified in the sorting specification (sort_spec) identifies the nth column in the result table. The number n must be less than or equal to the number of columns in the result table.

If a QUERY expression consists of more than one QUERY specification, sort specifications must be specified in one of the following forms:

Syntax Syntax

  1. <sort_spec> ::=
  <unsigned_integer> [ASC | DESC]
| <column_spec> [ASC | DESC]
End of the code.

As the column specification column_spec in this case, you can either use a reference name reference_name or a column name column_name from the list of selected columns of the first QUERY specification.

Scalar subqueries are not permissible in an ORDER clause.

ASC/DESC

The default setting is ASC.

  • ASC: The values are sorted in ascending order.

  • DESC: the values are sorted in ascending order.

Further Explanations

If a QUERY specification was specified with DISTINCT, the total of the internal lengths of all sorting columns must not exceed 1016 characters; otherwise it can comprise 1020 characters.

Column names in the sort specifications must be columns in the tables of the FROM clause or identify a result_column_name in the selected columns of the QUERY specification.

If DISTINCT or a set function was used in a selected column, the sort specification must identify a column in the result table.

Values are compared in accordance with the rules for the comparison predicate. Within sorting order, NULL values are greater than non-NULL values and special NULL values are greater than non-NULL values but less than NULL values.

More Information

Restrictions for SQL Statements

SELECT Statement (select_statement)

QUERY Expression (query_expression)