SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-217
Considerations for GROUP BY
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.
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.
Considerations for UNION
Suppose that the contributing SELECT statements are named SELECT1 and
SELECT2, the contributing tables resulting from the SELECT statements are named
TABLE1 and TABLE2, and the table resulting from the UNION operation is named
RESULT.
Characteristics of the UNION Columns
For columns in TABLE1 and TABLE2 that contribute to the RESULT table:
•
If both columns contain character strings, the corresponding column in RESULT
contains a character string whose length is equal to the greater of the two
contributing columns.
•
If both columns contain variable-length character strings, RESULT contains a
variable-length character string whose length is equal to the greater of the two
contributing columns.