SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-23
SELECT Statement
COLLATE { collation | CHARACTER SET }
specifies an alternate collating sequence that determines the ordering of rows in a
column specified on a GROUP BY or ORDER BY clause, temporarily overriding
the effect of any collation associated with the column as part of its table definition.
ORDER BY { colname } [ASC[ENDING]] [ collate ]
{ colnum } [DESC[ENDING]
specifies the order in which to sort the rows of the result table.
colname and colnum are as previously described for the GROUP BY clause, with
these additional restrictions:

If you specify DISTINCT, colname must be in select-list.

If you specify a GROUP BY or HAVING clause, the ordering column must also
be a grouping column.

If an ORDER BY clause applies to a union of SELECT statements, the
ordering column must be explicitly referenced, outside a function or an
expression, in select-list of the leftmost SELECT statement.
ASCENDING and DESCENDING specify the sort order. If you specify the ORDER
BY clause without ASCENDING or DESCENDING, the default is ASCENDING.
Be sure to specify the ORDER BY clause for each column you need ordered.
Otherwise, SQL determines the order of the column and does not guarantee a
specific or consistent order of rows. ORDER BY can reduce performance,
however, so use it only if you require a specific order.
For ordering a result table on a column that can contain null values, a null value is
considered equal to other null values but greater than all other nonnull values.
ORDER BY is valid only for the SELECT part of an INSERT statement or for a
SELECT statement used in a cursor declaration in a host program.
UNION [ ALL ] select-statement
specifies a set UNION operation between the result table of this SELECT
statement and the result table of another SELECT statement. The select-lists
in the two SELECT statements must have the same number of columns, and
columns in corresponding positions within the select-lists must have
comparable data types (for example, both numeric or both character types).
collation is the name of an existing collation that specifies a collating
sequence and uses the same character set as the
associated column.
CHARACTER
SET
specifies a collating sequence based on the binary value of
characters in the column.