RE: SQL question

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

I see.  Try modifying the Exceptions CTE to join back to the original file:

with                                                             
Uniques as                                                       
 (select address,count(*)                                        
  from myFile
  group by address                                               
  having count(*) = 1),                                          
BaseDupes as                                                     
 (select account,address,count(*)                                
  from myFile
  group by account,address                                       
  having count(*) > 1),                                          
Exceptions as                                                    
 (select a.account, a.address                                    
  from BaseDupes a join myFile b on a.address = b.address and 
       a.account <> b.account),                                  
Dupes as                                                         
 (select a.account, a.address                                    
  from BaseDupes a exception join Exceptions using(address,account))
select a.* from myFile a join uniques using(address)             
union all                                                          
select a.* from myFile a join dupes using(account,address)       
order by mykey                                                     

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL question

Thank you, Elvis.  When I ran your suggestion, I got:

KEY   ACCOUNT  ADDRESS 
  1      11    11 MAIN 
  3      11    11 MAIN 
  5      14    13 MAIN 
  7      16    15 MAIN 
  8      17    16 MAIN 
  9      16    15 MAIN 
 10      18    17 MAIN 

which is very nearly what I want.
Unfortunately, it grabs key 7 and 9, which
it should not because key 6 (which is wrong in
my e-mail and should be account 15, address 15 Main)
gives a different account number for the same address.

Your code gives me a whole to tool, CTE's, so I am going
to look into that approach. It seems, as you say, a good
way to break down the problem.

Thanks again,
Lance