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...
