It can be done...but it's not easy as SQL is designed such that a set of
records has no order, thus no "first record"....unless you specifically
give it order via the ORDER BY...
But it's gotten much easier since the OLAP functions were added...
I don't have time to do up an example, but google for RANK() and/or
ROW_NUMBER()
HTH,
CHarles
On Wed, Feb 6, 2013 at 4:48 PM, Gqcy <gmufasa01@xxxxxxxxx> wrote:
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...
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
