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