Try the following:
With sorted as (select usrinusr, usrinjob, usrinnbr, usrinlvl,
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
California Fine Wire
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Gqcy
Sent: Wednesday, February 06, 2013 12:43 PM
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
I want to return the values in the first record returned for the group record by usrinusr, usrinjob, usrinnbr...
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact