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.