7.24.2 Rules for specifying a sort specification list in an ORDER BY clause

Organization of this subsection

(1) Common rules
(2) Rules for specifying value expressions as sort keys

(1) Common rules

The sort keys can include a mixture of value expressions and sort item specification numbers.

Example:
```
SELECT "C1", AVG("C2") FROM "T1"
    GROUP BY "C1"
    ORDER BY "C1" ASC, 2 ASC
```
If you specify duplicate sort keys, the first order specification and null-value sort order specification takes precedence.

Example:
```
SELECT "C1","C2" FROM "T1"
    ORDER BY "C1" ASC NULLS FIRST,"C1" DESC NULLS LAST
```
In the above case, the underlined portion, which was specified first, takes precedence.
If the same column is specified two or more times in the selection list, it cannot be specified as a sort key. For example, the SQL statement below generates an error.

Example of an SQL statement that generates an error:
```
SELECT "C1","C2","C1" FROM "T1" ORDER BY "C1"
```
If a derived column name specified in an ORDER BY clause was derived from just a single column specification, it is replaced by that column specification. For example, the following three SQL statements produce the same retrieval results:
```
SELECT "T1"."C1" DR1,"T1"."C2" DR2 FROM "T1" ORDER BY DR1
SELECT "T1"."C1" DR1,"T1"."C2" DR2 FROM "T1" ORDER BY "T1"."C1"
SELECT "T1"."C1" DR1,"T1"."C2" DR2 FROM "T1" ORDER BY 1
```

To Page Top

(2) Rules for specifying value expressions as sort keys

You cannot specify a dynamic parameter by itself as a sort key.
If you specify a value expression as a sort key (unless the value expression is a column specification only), the sort key cannot include a derived column name (unless it is a derived column consisting of a simple column specification).

■ Example of an SQL statement that generates an error
```
SELECT "C1",SUM("C2") AS "SUMC2" FROM "T1"
    GROUP BY "C1"
    ORDER BY "SUMC2"+1
```
In addition, if a set operation is specified, the sort key cannot include the name of a derived column that was derived by means of the set operation (not even a derived column consisting of a simple column specification).

■ Example of an SQL statement that generates an error
```
SELECT "C1","C2" FROM "T1" UNION ALL SELECT "C1"+"C2","C3" FROM "T1"
    ORDER BY "C1"+"C2"
```
If you specify a value expression as the sort key of a SELECT statement that includes a set operation, the sort key must be identical to the sort key of the selection expressions in the first query specification.

■ Examples of correct SQL statements

Example 1:
```
SELECT "C1"+"C2","C3" FROM "T1" UNION SELECT "C1","C2" FROM "T2"
    ORDER BY "C1"+"C2"
SELECT "C1"+"C2","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2"
    ORDER BY "C2"
```
The SELECT statements in the above examples can be executed because the sort key value expression is identical to the sort key of the selection expressions in the first query specification.

Example 2:
```
SELECT "C1"+"C2" AS "C1","C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2"
    ORDER BY "C1"
```
When the sort key value expression is a column specification, as in the above example, you can execute the SELECT statement by specifying an AS clause.

■ Example of an SQL statement that generates an error
```
SELECT "C1","C2" FROM "T1" UNION SELECT "C1"+"C2","C2" FROM "T2"
    ORDER BY "C1"+"C2"
```
The above example generates an error because the sort key value expression is not identical to a selection expression in the first query specification.
In a SELECT statement with DISTINCT specified, the sort key value expression must be identical to one of the selection expressions.

■ Examples of correct SQL statements

Example 1:
```
SELECT DISTINCT "C1"+"C2","C2" FROM "T1" ORDER BY "C1"+"C2"
SELECT DISTINCT "C1"+"C2","C2" FROM "T1" ORDER BY "C2"
```
The SELECT statements in the above examples can be executed because the sort key value expression is identical to one of the selection expressions.

Example 2:
```
SELECT DISTINCT "C1"+"C2" AS "C1","C2" FROM "T1" ORDER BY "C1"
```
When the sort key value expression is a column specification, as in the above example, you can execute the SELECT statement by specifying an AS clause.

■ Example of an SQL statement that generates an error
```
SELECT DISTINCT "C1","C2" FROM "T1" ORDER BY "C1"+"C2"
```
The above example generates an error because the sort key value expression is not identical to a selection expression.
If you specify a window function in the sort key, the sort key value expression must be identical to a selection expression.

■ Example of a correct SQL statement
```
SELECT SUM("C1") OVER()/100 FROM "T1" ORDER BY SUM("C1") OVER()/100
```
The SELECT statement in the above example can be executed because the sort key value expression is also specified as a selection expression.

■ Example of an SQL statement that generates an error
```
SELECT SUM("C1") OVER() FROM "T1" ORDER BY SUM("C1") OVER()/100
```
The above example generates an error because the sort key value expression is not identical to a selection expression.
You cannot specify a subquery or dynamic parameter in the sort key value expression in the following circumstances:
- When a set operation is specified
- In a SELECT statement with DISTINCT specified
■ Example of an SQL statement that generates an error
```
SELECT "C1"+?,"C2" FROM "T1" UNION SELECT "C1","C2" FROM "T2"
    ORDER BY "C1"+?
```
The name of a table reference specified in the outermost query specification cannot be referenced from a subquery in the ORDER BY clause.

■ Example of an SQL statement that generates an error
```
SELECT * FROM "T1"
    ORDER BY "C1",(SELECT "C1" FROM "T2" WHERE "C2"="T1"."C2")+"C2"
```
To specify a set function in the sort key, one of the following conditions must be met:

(1) A grouping column must be specified in the selection expression of the qualified query of the set function.

(2) The column specifications included in the sort key value expression must be specified in a grouping column or aggregated argument.

■ Examples of correct SQL statements
```
SELECT "C1" FROM "T1" GROUP BY "C1" ORDER BY AVG("C2")
```
The above example meets condition (1).
```
SELECT "C2" FROM "T1" GROUP BY "C1","C2" ORDER BY SUM("C1"),"C2"
```
The above example meets condition (2).

■ Example of an SQL statement that generates an error
```
SELECT "C1" FROM "T1" ORDER BY AVG("C2")
```
If the same value expression is specified for both the sort key and selection expression, sorting is performed by using the value expression specified for the selection expression. The value expression specified for the sort key is not used for sorting.

Example:
```
SELECT "C1","C2" FROM "T1" ORDER BY "C1"
SELECT "C1"+"C2","C2" FROM "T1" ORDER BY "C1"+"C2"
```
In the above examples, sorting is done using the value expressions in the selection expressions.
When the sort key value expression differs from the selection expressions, sort processing is performed using the sort key value expression. However, HADB handles this internally by adding the sort key value expression as a selection expression before performing the sort processing.

Example:
```
SELECT "C1" FROM "T1" ORDER BY "C2"
```
In the above example, the values in column C1 are returned in a sorted order that is determined by sorting the values in column C2. HADB handles this internally by adding the sort key value expression (C2) as a selection expression before performing the sort processing. As a result, rules that restrict value expressions inside the query specification are also applied to the internally-added sort key value expression.

■ Example of an SQL statement that generates an error
```
SELECT MEDIAN("C1"*0.5) FROM "T1"
    ORDER BY PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY "C1"*0.5)
```
In the preceding example, although the underscored sort key is internally added to the selection expression, the statement results in an error due to a restriction on inverse distribution functions (a single query specification cannot include multiple inverse distribution functions for which a value expression that is not an independent column specification is specified as an aggregated argument).

To Page Top