Ordering by a Column or by an Expression

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;

HCL OneDB™ 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. 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.

In HCL OneDB, no column in the ORDER BY clause can be a collection type, but a query whose result set defines a collection-derived table can include the ORDER BY clause. For an example, 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.