SQL/MX Quick Start (G06.24+, H06.03+)

Displaying Information About Groups of Rows

HP NonStop SQL/MX Quick Start—523724-002

4-2

Grouping and Ordering Rows

Sometimes you want to display information about ordered groups of rows. For

example, you might want to display group information ordered by the result of an

aggregate function.

Example

Determine the earliest delivery date of all orders from the same customer. Order the

selected rows by that delivery date:

SELECT MIN(DELIV_DATE), CUSTNUM

FROM ORDERS

GROUP BY CUSTNUM

ORDER BY 1;

Some of the selected rows are:

(EXPR) Cust/Num

----------- --------

2003-04-10 1234

2003-06-15 7777

2003-07-01 926

... ...

2003-12-15 5635

--- 12 row(s) selected.

Tip

•

If an item in the select list is an expression, such as a function, the heading of the

column is (EXPR). If you want to arrange the rows of a report by the value of an

expression in the select list, you must refer to the expression as a number that

indicates where the expression appears in the list. In this example, ORDER BY 1

refers to MIN(DELIV_DATE).

•

When you group rows, each item you display must be either a column of the

GROUP BY clause (a grouping column) or a result from a function applied to a

column. In this example, CUSTNUM is the grouping column, and MIN is the

function applied to a nongrouping column.