|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.