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

Result can be:
AAA      A12                 5

Or

AAA      A15                 5 

But not both (See the BBB example).

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

Let me ask you this first.  If I add one more row
FIELD1   FIELD2    COUNTFIELD 
AAA      A15                 5
What should you see?
FIELD1   FIELD2    COUNTFIELD 
AAA      A12                 5
AAA      A15                 5
or
just the A15?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/22/2006 03:24 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Subject
SQL - select the record with the highest count value within a field






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