Page 1 of 1

'GROUP BY' Equivalent

PostPosted: Sat Apr 24, 2010 12:18 am
by kanaka
Appreciate if someone can put a quick solution .

I have a scenario as below

Input File -
Student-ID  Payment-Id     Payment
1           2              100
1           1              300
1           3              100
2           1              500
2           2              300

Output File -
Student-ID  Payment-Id     Payment
1           3              100
2           2              300


I want to retrieve a single record for each student having highest payment id

Please help me accomplish this .

Re: 'GROUP BY' Equivalent

PostPosted: Sat Apr 24, 2010 12:47 am
by dick scherrer
Hello,

Appreciate if someone can put a quick solution .
We don't have a plan for quick. Quick requires some luck as well as having a clear requirement. . .

Is this really a sort question?

I want to retrieve a single record for each student having highest payment id
Are these records in some file or rows in a table. . .?

Re: 'GROUP BY' Equivalent

PostPosted: Sat Apr 24, 2010 1:21 am
by kanaka
These records are in a file .

Re: 'GROUP BY' Equivalent

PostPosted: Sat Apr 24, 2010 1:24 am
by Frank Yaeger
kanaka,

You can use a DFSORT/ICETOOL job like the following to do what you asked for:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//IN DD *
1           2              100
1           1              300
1           3              100
2           1              500
2           2              300
/*
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) TO(OUT) ON(1,1,CH) FIRST USING(CTL1)
/*
//CTL1CNTL DD *
  SORT FIELDS=(1,1,CH,A,13,1,CH,D)
/*

Re: 'GROUP BY' Equivalent

PostPosted: Sat Apr 24, 2010 1:29 am
by kanaka
Thanks for your help !