Re: sql - group by clause, aggregate returning first record values for rest of fields.

[Gqcy <gmufasa01@xxxxxxxxx>]

Lets say I have 5 records that group together to 1:

ASSEMBLY,FTCH1505,778713,4,XXX312,U,2013-02-06-15.34.21.616000
ASSEMBLY,FTCH1505,778713,6,XXX312C,,2013-02-06-15.34.21.616000
ASSEMBLY,FTCH1505,778713,8,MENU00,,2013-02-06-15.34.21.617000
ASSEMBLY,FTCH1505,778713,9,MENU00,,2013-02-06-15.34.21.617000
ASSEMBLY,FTCH1505,778713,10,MENUCL02,,2013-02-06-15.34.21.617000

the MIN() query would return:
ASSEMBLY,FTCH1505,778713,4,MENU00,,2013-02-06-15.34.21.616000

I want to see:
ASSEMBLY,FTCH1505,778713,4,XXX312,U,2013-02-06-15.34.21.616000

It may be easier to say, I want to see the first record in a group...





On 2/6/2013 3:36 PM, Matt Olson wrote:
> So why didn't " select usrinusr, usrinjob, usrinnbr, min(usrinlvl), min(usrinpgm), min(usrintstmp) from mgpll/usrinpf1 group by usrinusr, usrinjob, usrinnbr order by usrinnbr"
>
> Work for you?
>
> -----Original Message-----
> From: Gqcy [mailto:gmufasa01@xxxxxxxxx]
> Sent: Wednesday, February 06, 2013 2:43 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: sql - group by clause, aggregate returning first record values for rest of fields.
>
> I have a file with 6 fields, I want to aggregate by the first three fields (jobname, user, jobnumber), I want to return the "first values found" for the rest of the fields in the file.
> I can get min() to kinda work, but that is field dependent.
>
>
> select usrinusr, usrinjob, usrinnbr, usrinlvl, usrinpgm, usrintstmp from mgpll/usrinpf1 group by usrinusr, usrinjob, usrinnbr order by usrinnbr
>
> I want to return the values in the first record returned for the group record by usrinusr, usrinjob, usrinnbr...