Re: finding duplicates via sql-help

From: Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
Subject: finding duplicates via sql-help
To: "Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)" <midrange-l@xxxxxxxxxxxx>
Received: Friday, September 14, 2012, 9:24 PM
This may seem kind of odd.  I'm
trying to find duplicates in a file although they aren't
technically duplicates.

Example Data    --- the data below would result in
duplicates being found.

SITEID             
     ADDRPRF       
     STREET       
          STS
101               
         120   
               
     MAIN ST       
       AC
105               
         120   
               
    #MAIN ST         
   SS
110               
         101   
               
    #FIRST  ST       
     SS
150               
         101   
               
   FIRST ST         
       CL
Ive done this with query, but I can't seem to figure it out
with SQL

What I want to do is find  streets that have a 
prefix of (#) and determine if I have a duplicate
address   without the prefix of (#). 
Then listing all SITEID's and addresses that are dups.

I'm thinking this is a case for a CTE but I've not used them
much.

Any ideas.
NOTICE: This message, including any attachment, is intended
as a confidential and privileged communication. If you have
received this message in error, or are not the named
recipient(s), please immediately notify the sender and
delete this message.
--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.