SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-195
Considerations for GROUP BY
The RANDOM function cannot be part of the SEQUENCE BY clause of a sampling
method.
Considerations for GROUP BY
•
If you include a GROUP BY clause, the columns you refer to in the select-list
must be either grouping columns or arguments of an aggregate (or set) function.
For example, if AGE is not a grouping column, you can refer to AGE only as the
argument of a function, such as AVG (AGE).
•
If you do not include a GROUP BY clause but you specify an aggregate function in
the select-list, all rows of the result table form the one and only group. The
result of AVG, for example, is a single value for the entire table.
•
The GROUP BY clause must precede a HAVING clause.
•
The RANDOM function cannot be part of the GROUP BY clause of a sampling
method.
Considerations for SORT BY
•
The RANDOM function cannot be part of the SORT BY clause of a sampling
method.
Considerations for ORDER BY
When you specify an ORDER BY clause and its ordering columns, consider this:
•
If you specify DISTINCT, the ordering column must be in select-list.
•
If you specify a GROUP BY 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, and not within an aggregate function or an
expression, in the select-list of the leftmost SELECT statement.
•
SQL does not guarantee a specific or consistent order of rows unless you specify
an ORDER BY clause. ORDER BY can reduce performance, however, so use it
only if you require a specific order.
•
The RANDOM function cannot be part of the ORDER BY clause of a sampling
method.
Considerations for DISTINCT
•
The RANDOM function cannot be part of the DISTINCT clause of a sampling
method.