SELECT with first n duplicates

ICETOOL's SELECT operator now allows you to select the first n records with each key or the first n duplicate records with each key.

As example, following SELECT operator could be used to list the top 3 students in each class.
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,10,CH) FIRST(3) USING(CTL1)
//CTL1CNTL DD *
SORT FIELDS=(1,10,CH,A,21,2,ZD,D)

We sort on the class name in ascending order, and on the average in descending order, to get the records in order by class name and highest to lowest average. We SELECT on the class name and use FIRST(3) to get the first three records for each class which gives us up to three students with the highest average.

I/P records :
Geometry Fred 85
Geometry Janis 71
Geometry Leonard 78
Geometry Michael 91
Geometry Susan 83
Geometry William 92
Algebra Fred 83
Algebra Janis 90
Algebra Leonard 85
Algebra Michael 94
Algebra Susan 92
Algebra William 87

O/P records:
Algebra Michael 94
Algebra Susan 92
Algebra Janis 90
Geometry William 92
Geometry Michael 91
Geometry Fred 85

Syntax :- The syntax for the new operands of SELECT is as follows:
SELECT FROM(DDIN) TO(DDOUT) ON(1,5,CH) FIRST(x)FIRSTDUP(x)
Detailed Description :

• FIRST(x) - Limits the records selected to those with ON values that occur only once (value count = 1) and the first x records of those with ON values that occur more than once (value count > 1).
• FIRSTDUP(x) - Limits the records selected to the first x records of those with ON values that occur more than once (value count > 1).

Hope you find this post useful. Please do provide your comments or reactions. Any sort of feedback is appreciated. You may post your queries in comments as well. For further detailed reference on SELECT and other new ICETOOL functions,Click Here.