Release-Informationen

42.8 ORDER BY in Subselects

DB2 now supports ORDER BY in subselects and fullselects.

42.8.1 fullselect

Following is a partial syntax diagram of the modified fullselect showing the location of the order-by-clause.

>>-+-subselect---------+---------------------------------------->
   +-(fullselect)------+
   '-| values-clause |-'
 
   .----------------------------------------------.
   V                                              |
>----+------------------------------------------+-+------------->
     '-+-UNION---------+--+-subselect---------+-'
       +-UNION ALL-----+  +-(fullselect)------+
       +-EXCEPT--------+  '-| values-clause |-'
       +-EXCEPT ALL----+
       +-INTERSECT-----+
       '-INTERSECT ALL-'
 
>--+-----------------+-----------------------------------------><
   '-order-by-clause-'
 
 

A fullselect that contains an ORDER BY clause cannot be specified in:

• A summary table
• The outermost fullselect of a view (SQLSTATE 428FJ SQLCODE -20211)

An ORDER BY clause in a fullselect does not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.

42.8.2 subselect

Following is the complete syntax diagram of the modified subselect showing the location of the order-by-clause and the fetch-first-clause.

>>-select-clause--from-clause--+--------------+----------------->
                               '-where-clause-'
 
>--+-----------------+--+---------------+----------------------->
   '-group-by-clause-'  '-having-clause-'
 
>--+-----------------+-----------------------------------------><
   '-order-by-clause-'
 
 

The clauses of the subselect are processed in the following sequence:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

A subselect that contains an ORDER BY cannot be specified:

• In the outermost fullselect of a view
• In a summary table
• Unless the subselect is enclosed in parentheses

For example, the following is not valid (SQLSTATE 428FJ SQLCODE -20211):

SELECT * FROM T1
   ORDER BY C1
UNION
SELECT * FROM T2
   ORDER BY C1

The following example is valid:

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

An ORDER BY clause in a subselect does not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.

42.8.3 order-by-clause

Following is the complete syntax diagram of the modified order-by-clause.

             .-,------------------------------.
             V             .-ASC--.           |
>>-ORDER BY----+-sort-key--+------+---------+-+----------------><
               |           '-DESC-'         |
               '-ORDER OF--table-designator-'
 
sort-key
 
|--+-simple-column-name--+--------------------------------------|
   +-simple-integer------+
   '-sort-key-expression-'
 
 

ORDER OF table-designator

Specifies that the same ordering used in table-designator should be applied 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 subselect (or fullselect) corresponding to the specified table-designator must include an ORDER BY clause that is dependant on the data (SQLSTATE 428FI SQLCODE -20210). 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. For more information on table designators, see "Column Name Qualifiers to Avoid Ambiguity" in the SQL Reference.

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

42.8.4 select-statement

Following is the complete syntax diagram of the modified select-statement:

>>-+-----------------------------------+--fullselect------------>
   |       .-,-----------------------. |
   |       V                         | |
   '-WITH----common-table-expression-+-'
 
>--fetch-first-clause--*--+--------------------+---------------->
                          +-read-only-clause---+
                          |               (1)  |
                          '-update-clause------'
 
>--*--+---------------------+--*--+--------------+-------------><
      '-optimize-for-clause-'     '-WITH--+-RR-+-'
                                          +-RS-+
                                          +-CS-+
                                          '-UR-'
 
 

Anmerkungen:

1. The update-clause cannot be specified if the fullselect contains an order-by-clause.

SELECT INTO statement

Syntax

                        .-,-------------.
                        V               |
>>-select-clause--INTO----host-variable-+--from-clause---------->
 
>--+--------------+--+-----------------+--+---------------+----->
   '-where-clause-'  '-group-by-clause-'  '-having-clause-'
 
>--+-----------------+--+--------------+-----------------------><
   '-order-by-clause-'  '-WITH--+-RR-+-'
                                +-RS-+
                                +-CS-+
                                '-UR-'
 
 

42.8.5 OLAP Functions (window-order-clause)

Following is a partial syntax diagram for the OLAP functions showing the modified window-order-clause.

window-order-clause
 
             .-,--------------------------------------------.
             V                        .-| asc option |--.   |
|--ORDER BY----+-sort-key-expression--+-----------------+-+-+---|
               |                      '-| desc option |-' |
               '-ORDER OF--table-designator---------------'
 
asc option
 
        .-NULLS LAST--.
|--ASC--+-------------+-----------------------------------------|
        '-NULLS FIRST-'
 
desc option
 
         .-NULLS FIRST-.
|--DESC--+-------------+----------------------------------------|
         '-NULLS LAST--'
 
 

ORDER BY (sort-key-expression,...)

Defines the ordering of rows within a partition that determines the value of the OLAP function or the meaning of the ROW values in the window-aggregation-group-clause (it does not define the ordering of the query result set).

sort-key-expression

An expression used in defining the ordering of the rows within a window partition. Each column name referenced in a sort-key-expression must unambiguously reference a column of the result set of the subselect, including the OLAP function (SQLSTATE 42702 or 42703). The length of each sort-key-expression must not be more than 255 bytes (SQLSTATE 42907). A sort-key-expression cannot include a scalar fullselect (SQLSTATE 42822) or any function that is not deterministic or that has an external action (SQLSTATE 42845). This clause is required for the RANK and DENSE_RANK functions (SQLSTATE 42601).

ASC

Uses the values of the sort-key-expression in ascending order.

DESC

Uses the values of the sort-key-expression in descending order.

NULLS FIRST

The window ordering considers null values before all non-null values in the sort order.

NULLS LAST

The window ordering considers null values after all non-null values in the sort order.

ORDER OF table-designator

Specifies that the same ordering used in table-designator should be applied 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 subselect (or fullselect) corresponding to the specified table-designator must include an ORDER BY clause that is dependent on the data (SQLSTATE 428FI SQLCODE -20210). 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. For more information on table designators, see "Column Name Qualifiers to Avoid Ambiguity" in the SQL Reference.