Example 9
This example shows how you can use USING(xxxx) to supply a SORT
statement to alter the records that are selected.
SELECT FROM(IN) TO(OUT) ON(1,5,CH) FIRST(3) USING(CTL1)
Let's say the IN data set
looks like this: FRANK 00015
FRANK 00012
FRANK 00003
FRANK 00018
FRANK 00005
FRANK 00035
VICKY 00022
VICKY 00007
VICKY 00014
VICKY 00028
VICKY 00002
VICKY 00015
We want to select the three records
with each name that have the highest counts. If we just used ON(1,5,CH)
without any CTL1CNTL statements, we'd get the first three records
for each name without regard to the count. The OUT data set would
look like this: FRANK 00015
FRANK 00012
FRANK 00003
VICKY 00022
VICKY 00007
VICKY 00014
To get the three records with the
highest counts for each name, we can use the following SORT statement
in CTL1CNTL: SORT FIELDS=(1,5,CH,A,7,5,ZD,D)
The records
will be sorted in ascending order on the name field, and in descending
order on the count field. By sorting descending on the count, we
ensure that the three records with the highest counts
are the first three records for each name. Thus, when ON(1,5,CH) selects
the first three records, they will be those with the highest
counts. The OUT data set will look like this: FRANK 00035
FRANK 00018
FRANK 00015
VICKY 00028
VICKY 00022
VICKY 00015