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

[Gqcy <gmufasa01@xxxxxxxxx>]

Thanks Tom,
this worked perfectly.
Except we should use "parTITion" not "partion"..

:)



On 2/6/2013 3:42 PM, Tom E Stieger wrote:
> Try the following:
>
> With sorted as (select usrinusr, usrinjob, usrinnbr, usrinlvl, usrinpgm, usrintstmp,
> Row_number() over(partion by usrinusr, usrinjob, usrinnbr) as rowno
> from mgpll/usrinpf1 )
> select usrinusr, usrinjob, usrinnbr, usrinlvl, usrinpgm, usrintstmp from sorted where rowno = 1
>
>
> -Tom Stieger
> IT Manager
> California Fine Wire
>
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Gqcy
> Sent: Wednesday, February 06, 2013 12: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...