RE: SQL help - Select partial distinct...

Charles,

Great!  I just tried this method in interactive SQL (after it not
seeming to work right while embedded) and it works great.  Now I just
need to tweak my embedded code.  Thanks for the help.

Kurt Anderson
Application Developer
Highsmith Inc

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Thursday, November 30, 2006 2:38 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL help - Select partial distinct...

Kurt,

I usually do stuff like this using a common table expression (CTE)

With onerec as (select key, min(rrn(myfile)) as theRRN
                from myfile
                group by key
               )
Select B.key, B.fld1, B.fld2

From onerec A, myfile B

Where A.key = B.key
  and A.theRRN = rrn(B)

HTH,



Charles Wilt
--
iSeries Systems Administrator / Developer Mitsubishi Electric Automotive
America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kurt Anderson
Sent: Thursday, November 30, 2006 3:01 PM
To: RPG programming on the AS400 / iSeries
Subject: SQL help - Select partial distinct...

I have a declare statement that I use for a fetch.
I am selecting INTO a host data structure, so I need to list out all 
the fields I want in the select statement.
But I only want the 1st record for each duplicate key.  
 
ex:
1000, Blahblah.
1000, Blahblah again
2000, anotherone
2000, anothertwo
2000, anotherthree
 
So I want the select statement to only return the 1st and 3rd row, not

all rows, and I need both fields to return to the host data structure.
 
Is the solution that I have to retrieve the key value by itself, then 
use that to retrieve the attached information?  I'd have no issue 
doing this with RPG I/O but I think the Where clause performance would
outweigh RPG.   (Like vs %scan)
 
Thanks,
 
Kurt Anderson
Application Developer
Highsmith Inc.
W5527 State Road 106, P.O. Box 800
Fort Atkinson, WI 53538-0800
TEL (920) 563-9571  FAX (920) 563-7395 EMAIL kjanderson@xxxxxxxxxxxxx
 
--
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.