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

Yeah, maybe I misunderstood... from what I understand, Lim, just wants one
record returned.

Something like, if you had a DDS where Count Field was in descending order,
followed by Field1 in ascending order.

Then you can do a

SETGT *HIVAL FILE
ReadP FILE
and then you'll have your one record.  That's how I took her question.

Even with distinct or a Max, you still have the possibility of there being
more than just one record being processed by the SQL.

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:45:00 PM:

Something about DISTINCT or MAX comes to mind.

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





Michael_Schutte@xxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/22/2006 03:42 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

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

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