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

Sorry some corrections if forgot some qualifiers in the second statement:

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 a.CountField = MaxCount
              group by a.Field1, MaxCount)
select y.*
from MyTable y join b 
     on y.Field1 = b.Field1 and Field2 = MaxField2 and CountField = MaxCount

Mit freundlichen Gru?en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von HauserBirgitta
Gesendet: Friday, September 22, 2006 21:52
An: RPG programming on the AS400 / iSeries
Betreff: AW: SQL - select the record with the highest count value within
afield


Hallo,

you may try the following:
with a as (select Field1, Max(CountField) as MaxCount
              from MyTable
              group by Field1)
select x.*
from MyTable x join a on x.Field1 = a.Field1

This statement returns:
AAA A12 5
BBB B17 5
BBB B13 5

If you need only on row with BBB with the maximum value of Field2, 
you may use the following statement:
with a as (select Field1, Max(CountField) as MaxCount
              from MyTable
              group by Field1)
     b as (Select Field1, Maxcount, Max(Field2) as MaxField2
              from a join MyTable x
                on a.Field1 = x.Field1 and a.CountField = MaxCount
              group by Field1, MaxCount)
select y.*
from MyTable y join b 
     on y.Field1 = b.Field1 and Field2 = MaxField2 and CountField = MaxCount

This statement should return:
AAA A12 5
BBB B17 5

I did not try it out, because I have no access to an iSeries.

Mit freundlichen Gru?en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Lim Hock-Chai
Gesendet: Friday, September 22, 2006 21:37
An: RPG programming on the AS400 / iSeries
Betreff: RE: SQL - select the record with the highest count value within
afield


Yes SQL.  

Your sql statement will select all records.  I need only the first
highest countfield value record within FIELD1.  


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

Using SQL????


Select field1, field2, countfield,
into  :field1, :field2, :countfield
from FILE
Order by countfield descend, field1

I hope that's what you was asking for.


Michael Schutte
Admin Professional
Dig in! Bob Evans New Knife & Fork Sandwiches - This much homestyle
goodness demands a Knife & Fork!


rpg400-l-bounces@xxxxxxxxxxxx wrote on 09/22/2006 03:24:45 PM:

is there a way to select the first record with the highest count value

within a field?

Example:
Data:
FIELD1   FIELD2    COUNTFIELD
AAA      A12       5
AAA      A13       2
AAA      A14       1
BBB      B17       5
BBB      B13       5
BBB      B15       1

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

--
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.

--
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.