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

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)

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

Forgive me.  Can I make it a bit more challenging by adding one more 
field to the data?
Example:
Data:
FIELD1   FIELD2    FIELD3    COUNTFIELD
AAA      A12       D1          5
AAA      A13       D5          2
AAA      A14       D2          1
BBB      B17       CC          5
BBB      B13       DC          5
BBB      B15       D1          1

Result needed (Assuming FIELD1 is the field where the highest 
COUNTFIELD need to select from)
FIELD1   FIELD2    FIELD3   COUNTFIELD
AAA      A12       D1          5
BBB      B17       CC          5

Or

FIELD1   FIELD2    FIELD3   COUNTFIELD
AAA      A12       D1          5
BBB      B13       DC          5

I think to add a third field as you mentioned, you would just need to
extend Birgitta's example with one more Common Table Expression:

     c as (Select  b.Field1, b.maxfield2, Maxcount, Max(Field3) as
MaxField3
              from b join MyTable x
                on b.Field1 = x.Field1 and b.maxfield2=x.field2 and
a.CountField = MaxCount) select y.* from MyTable y join c 
     on y.Field1 = c.Field1 and y.Field2 = c.MaxField2 and y.field3 =
c.MaxField 3 and CountField = MaxCount

I think a simliar technique would work for n fields, but as some point
this becomes really ugly and you probably want to consider using record
I/O and SETGT.

HTH,
Adam





########################################################################
#############
Attention:
The above message and/or attachment(s) is private and confidential and
is intended only for the people for which it is addressed. If you are
not named in the address fields, ignore the contents and delete all the
material. Thank you. Have a nice day.

For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
########################################################################
#############
--
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.