Ordering by a Column or by an Expression

For Extended Parallel Server, any column in the ORDER BY clause must also appear explicitly or by * notation in the select list of the Projection clause. To order query results by an expression, you must also declare a display label for the expression in the Projection clause, as in the following example, which declares the display label span for the difference between two columns:

SELECT paid_date - ship_date span, customer_num FROM orders
   ORDER BY span

Dynamic Server supports columns and expressions in the ORDER BY clause that do not appear in the select list of the Projection clause. You can omit a display label for the derived column in the select list and specify the derived column by means of a select number in the ORDER BY clause.

The select list of the Projection clause must include any column or expression that the ORDER BY clause specifies, however, if any of the following is true:

• The query includes the DISTINCT, UNIQUE, or UNION operator.
• The query includes the INTO TEMP table clause.
• The distributed query accesses a remote database whose server requires every column or expression in the ORDER BY clause to also appear in the select list of the Projection clause.
• An expression in the ORDER BY clause includes a display label for a column substring. (See the next section, Ordering by a Substring.)

The next query selects one column from the orders table and sorts the results by the value of another column. (With Extended Parallel Server, order_date must also appear in the Projection clause.) By default, the rows are listed in ascending order.

SELECT ship_date FROM orders ORDER BY order_date

You can order by an aggregate expression only if the query also has a GROUP BY clause. This query declares the display label maxwgt for an aggregate in the ORDER BY clause:

SELECT ship_charge, MAX(ship_weight) maxwgt
   FROM orders GROUP BY ship_charge ORDER BY maxwgt

If the current processing locale defines a localized collation, then NCHAR and NVARCHAR column values are sorted in that localized order.

3In Dynamic Server, no column in 3the ORDER BY clause can be a collection type, but a query whose result set 3defines a collection-derived table can include the ORDER BY clause. For an 3example, see Collection-Derived Table.

You might improve the performance of some non-PDQ queries that use the ORDER BY clause to sort a large set of rows if you increase the setting of the DS_NONPDQ_QUERY_MEM configuration parameter.