SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-20
Selecting Distinct Rows
+> GROUP BY JOBCODE, DEPTNUM;
JOBCODE DEPTNUM (EXPR)
------- ------- -------------------
100 1000 1
100 1500 1
500 1000 3
600 1500 2
900 1000 1
900 1500 1
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-14.
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.
The following 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 SALES.ODETAIL;
PARTNUM
-------
212
244
255
2001
.
.
You can expand this query to print the part descriptions also:
>> SELECT DISTINCT P.PARTNUM, PARTDESC
+> FROM SALES.PARTS P, SALES.ODETAIL O
+> WHERE P.PARTNUM = O.PARTNUM;
PARTNUM PARTDESC
------- ------------------
212 PC SILVER, 20 MB
244 PC GOLD, 30 MB
. .
. .
The following examples illustrate different ways of selecting distinct values.