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

Yes it will select all records, but it will only put the first record found
into the fields.  I don't know of a way just to select one record.






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:37:17 PM:

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.


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