DB2 now supports ORDER BY in subselects and fullselects.
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:
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.
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:
A subselect that contains an ORDER BY cannot be specified:
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.
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-'
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
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:
Syntax
.-,-------------. V | >>-select-clause--INTO----host-variable-+--from-clause----------> >--+--------------+--+-----------------+--+---------------+-----> '-where-clause-' '-group-by-clause-' '-having-clause-' >--+-----------------+--+--------------+----------------------->< '-order-by-clause-' '-WITH--+-RR-+-' +-RS-+ +-CS-+ '-UR-'
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--'