Re: SQL - select the record with the highest count value within afield -- RPG400-L

Hi,

if you are already on release V5R4 you may also try the following statementwhich looks less nasty:

With x as (Select Row_Number()
                 Over(Partition By Field1

Order By CountField Desc, Field1 Desc, Field2 Desc,Field3 Desc) as RowCount,

                 a.*
             from MyTable a)
Select Field1, Field2, Field3, CountField
  from x
  Where RowCount = 1;

Mit freunlichen Gruessen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (LesBrown)

"If you think education is expensive, try ignorance." (Derek Bok)

----- Original Message -----From: "Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>

To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, September 22, 2006 22:51

Subject: RE: SQL - select the record with the highest count value withinafield

You are correct.  It results in what I needed.  Below is the complete
and running sql statement (hhhmmm, as you mentioned, native io might be
the better route):

with a as (select Field1, Max(CountField) as MaxCount
             from MyTable
             group by Field1),
    b as (Select  a.Field1, Maxcount, Max(Field2) as MaxField2
             from a join MyTable x
               on a.Field1 = x.Field1 and MaxCount = x.CountField
             group by a.Field1, MaxCount),
    c as (Select  b.Field1, b.maxfield2, B.Maxcount, Max(Field3) as
                  MaxField3
             from b join MyTable y
               on b.Field1 = y.Field1 and b.maxfield2=y.field2 and
                  b.MaxCount=y.CountField
              group by b.Field1, b.maxfield2, MaxCount)
select z.* from MyTable z join c
    on z.Field1 = c.Field1 and z.Field2 = MaxField2 and
    z.Field3 = MaxField3 and z.CountField = c.MaxCount


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
AGlauser@xxxxxxxxxxxx
Sent: Friday, September 22, 2006 3:28 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL - select the record with the highest count value within
afield

Wouldn't the last join condition take care of that?

rpg400-l-bounces@xxxxxxxxxxxx wrote on 22/09/2006 04:22:27 PM:

I haven't try it but I think it would result in:
AAA      A12       D1          5
BBB      B17       DC          5 (incorrect result, this record does

not

exist in original file)



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.