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

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.