RE: SQL - select the record with the highest count value within afield

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)