× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.