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

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—523725-004
7-20
Examples of SEQUENCE BY
Examples of SEQUENCE BY
•
Sequentially number each row for the entire result and also number the rows for
each part number:
SELECT RUNNINGCOUNT(*) AS RCOUNT, MOVINGCOUNT(*,
ROWS SINCE (d.partnum<>THIS(d.partnum)))
AS MCOUNT,
d.partnum
FROM orders o, odetail d
WHERE o.ordernum=d.ordernum
SEQUENCE BY d.partnum, o.order_date, o.ordernum
ORDER BY d.partnum, o.order_date, o.ordernum;
RCOUNT MCOUNT Part/Num
-------------------- --------------------- --------
1 1 212
2 2 212
3 1 244
4 2 244
5 3 244
... ... ...
67 1 7301
68 2 7301
69 3 7301
70 4 7301
--- 70 row(s) selected.
•
Show the orders for each date, the amount for each order item and the moving
total for each order, and the running total of all the orders. The query sequences
orders by date, order number, and part number. (The CAST function is used for
readability only.)
SELECT o.ordernum,
CAST (MOVINGCOUNT(*,ROWS SINCE(THIS(o.ordernum) <>
o.ordernum)) AS INT) AS MCOUNT,
d.partnum, o.order_date,
(d.unit_price * d.qty_ordered) AS AMOUNT,
MOVINGSUM (d.unit_price * d.qty_ordered,
ROWS SINCE(THIS(o.ordernum)<>o.ordernum)) AS ORDER_TOTAL,
RUNNINGSUM (d.unit_price * d.qty_ordered) AS TOTAL_SALES
FROM orders o, odetail d
WHERE o.ordernum=d.ordernum
SEQUENCE BY o.order_date, o.ordernum, d.partnum
ORDER BY o.order_date, o.ordernum, d.partnum;
Order/Num MCOUNT Part/Num Order/Date
AMOUNT ORDER_TOTAL TOTAL_SALES
---------- ----------- -------- ----------
---------- -------------- --------------
100250 1 244 1997-01-23
14000.00 14000.00 14000.00
100250 2 5103 1997-01-23