SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-18
SEQUENCE BY Clause
SEQUENCE BY Clause
Considerations for SEQUENCE BY
Examples of SEQUENCE BY
The SEQUENCE BY clause of the SELECT statement specifies the order in which to
sort the rows of the intermediate result table for calculating sequence functions. This
option is used for processing time-sequenced rows in data mining applications. See
SELECT Statement on page 2-178.
SEQUENCE BY is an SQL/MX extension.
colname
names a column in select-list or a column in a table reference in the FROM
clause of the SELECT statement. colname is optionally qualified by a table, view,
or correlation name; for example, CUSTOMER.CITY.
ASC | DESC
specifies the sort order. ASC is the default. For ordering an intermediate result
table on a column that can contain null, nulls are considered equal to one another
but greater than all other nonnull values.
You must include a SEQUENCE BY clause if you include a sequence function in the
select list of the SELECT statement. Otherwise, NonStop SQL/MX returns an error.
Further, you cannot include a SEQUENCE BY clause if there is no sequence function
in the select list. See Sequence Functions on page 9-7.
Considerations for SEQUENCE BY
•
Sequence functions behave differently from set (or aggregate) functions and
mathematical (or scalar) functions.
•
If you include both SEQUENCE BY and GROUP BY clauses in the same SELECT
statement, the values of the sequence functions must be evaluated first and then
become input for aggregate functions in the statement.
°
For a SELECT statement that contains both SEQUENCE BY and GROUP BY
clauses, you can nest the sequence function in the aggregate function:
SELECT ordernum,
MAX(MOVINGSUM(qty_ordered, 3)) AS maxmovsum_qty,
AVG(unit_price) AS avg_price
FROM odetail
SEQUENCE BY partnum
GROUP BY ordernum;
SEQUENCE BY colname [ASC[ENDING] | DESC[ENDING]]
[,colname [ASC[ENDING] | DESC[ENDING]]]...