'GROUP BY' Equivalent



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

'GROUP BY' Equivalent

Postby kanaka » Sat Apr 24, 2010 12:18 am

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 .
kanaka
 
Posts: 3
Joined: Fri Apr 23, 2010 11:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: 'GROUP BY' Equivalent

 

Re: 'GROUP BY' Equivalent

Postby dick scherrer » Sat Apr 24, 2010 12:47 am

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. . .?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: 'GROUP BY' Equivalent

Postby kanaka » Sat Apr 24, 2010 1:21 am

These records are in a file .
kanaka
 
Posts: 3
Joined: Fri Apr 23, 2010 11:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: 'GROUP BY' Equivalent

Postby Frank Yaeger » Sat Apr 24, 2010 1:24 am

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)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

Re: 'GROUP BY' Equivalent

Postby kanaka » Sat Apr 24, 2010 1:29 am

Thanks for your help !
kanaka
 
Posts: 3
Joined: Fri Apr 23, 2010 11:55 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post