Release Notes

|43.14 ORDER BY in Subselects

|DB2 now supports ORDER BY in subselects and fullselects.

|43.14.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.

|43.14.2 subselect

|Following is the complete syntax diagram of the modified subselect showing |the location of the order-by-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.

|43.14.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

|

|43.14.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-'
| 
| 

|Notes:

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-'
| 
| 

|43.14.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. |