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