NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-22
SELECT Statement
See Locking on page L-44 for more information about locking.
GROUP BY { colname | colnum }
specifies columns of the result table from the preceding FROM or WHERE clause
that define a set of groups in which each group consists of rows with identical
values in the specified columns.
For example, if you specify AGE, the result table contains one group of rows with
AGE equal to 40 and one group with AGE equal to 50. If you specify AGE and
JOB, the result table contains one group for each different age and job code pair.
When you refer to a grouping column in a search condition or expression, you refer
to a single value because each row in the group contains the same value in the
grouping column.
For the purpose of grouping, all null values are considered equal to one another. The
result table of a GROUP BY clause can have only one null group.
If the FROM clause specifies a grouped view, you cannot specify a GROUP BY
clause.
colname
is the name of a single column from a table in the FROM clause, optionally
qualified by a table name, view name, or correlation name: for example,
CUSTOMER.CITY.
colnum
is a positive integer that specifies a column by its position in the select-
list. Use colnum to refer to unnamed columns, such as expressions.
In a GROUP BY clause, you cannot use colnum to specify a column that is an
expression that contains a function if the argument of the function is a column of the
FROM clause result table. You can use colnum if the argument is a correlated
reference to a column from an outer query.
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.
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.