SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-21
Selecting Distinct Rows
The group to which the function is applied is determined by all the grouping columns
you specify. The last column you specify determines how precisely the groups are
divided.
Note that you do not have to specify an ORDER BY clause unless you want the groups
arranged in a particular order or you want to specify break groups in the report. For
more information, see Organizing Rows Into Break Groups on page 4-15.
Selecting Distinct Rows
The rows you retrieve with a SELECT command can contain duplicate values. If you
want only one entry in your report for each distinct value, you can specify the
DISTINCT keyword preceding the select list.
This query reports which parts are currently on order. The ODETAIL table contains two
rows for part number 212 and six rows for part number 244. The query selects only
one row for each part.
>> SELECT DISTINCT PARTNUM
+> FROM ODETAIL;
PARTNUM
-------
212
244
255
2001
.
.
You can expand this query to also print part descriptions:
>> SELECT DISTINCT P.PARTNUM, PARTDESC
+> FROM PARTS P, ODETAIL O
+> WHERE P.PARTNUM = O.PARTNUM;
PARTNUM PARTDESC
------- ------------------
212 PC SILVER, 20 MB
244 PC GOLD, 30 MB
. .
. .
These examples illustrate different ways of selecting distinct values:
•
The PARTSUPP table contains rows that record a part number, the supplier
number, the part cost, and the quantity received. If the supplier changes the cost of
Note. You do not have to specify an ORDER BY clause unless you want the groups
arranged in a particular order or you want to specify break groups in the report. For
more information, see Organizing Rows Into Break Groups on page 4-15.