Neoview SQL Reference Manual (R2.4)

AGE is not a grouping column, you can refer to AGE only as the argument of a function,
such as AVG (AGE).
• The expression in the GROUP BY clause must be exactly the same as the expression in the
select list. An error will be returned if it is not. It cannot contain aggregate functions or
subqueries.
• If the value of col-expr is a numeric constant, it refers to the position of the select list item
and is treated as the current GROUP BY using the ordinal feature.
• You can specify GROUP BY using ordinals to refer to the relative position within the SELECT
list. For example, GROUP BY 3, 2, 1.
• 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.
Considerations for ORDER BY
When you specify an ORDER BY clause and its ordering columns, consider:
• 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.
• If both columns are of exact numeric data types, RESULT contains an exact numeric value
whose precision and scale are equal to the greater of the two contributing columns.
• If both columns are of approximate numeric data types, RESULT contains an approximate
numeric value whose precision is equal to the greater of the two contributing columns.
• If both columns are of datetime data type (DATE, TIME, or TIMESTAMP), the corresponding
column in RESULT has the same data type.
• If both columns are INTERVAL data type and both columns are year-month or day-time,
RESULT contains an INTERVAL value whose range of fields is the most significant start
field to the least significant end field of the INTERVAL fields in the contributing columns.
SELECT Statement 175