DUPLICATE ADDRESS PROBLEM

[osimini1]

Please can somebody help or direct me on how to solve this problem with duplicate address A suitation where a provider_ID have two addresses in the same address, one with suite number and other without suite number. I want to issolate or remove the duplicate address without suite number. I have tried using SQL scripts but unable. Attached below is the sample data. I would appreciate any help that I can get.



ID FirstName LastName Address City Zip
AB74960 PETER AMES 1818 N ORANGE GROVE AVE STE 5000 POMONA 91767
AB74960 PETER AMES 1818 N ORANGE GROVE AVE POMONA 91767
AB74960 PETER AMES 12360 PACIFIC AVE LONG BEACH 90806
CK55566 KATHY TOBINS 2777 PACIFIC AVE STE E LONG BEACH 90806
CK55566 KATHY TOBINS 17660 LAKEWOOD BLVD BELLFLOWER 90706
CK55566 KATHY TOBINS 2360 PACIFIC AVE STE 200 LONG BEACH 90806
CK55566 KATHY TOBINS 2777 PACIFIC AVE LONG BEACH 90806
GKL81919 KATE PAST 1000 W WHITTIER BLVD MONTEBELLO 90640
GKL81919 KATE PAST 3030 TYLER AVE STE 12 EL MONTE 91731
GKL81919 KATE PAST 1000 W WHITTIER BLVD STE 34 MONTEBELLO 90640
GKL81919 KATE PAST 3030 TYLER AVE EL MONTE 91731

 

[SkipVought]

I would start using a query Counts occurances of ID, City & Zip like this

Select ID, City, Zip, Count(*)
From [YourTable]
Group By ID, City, Zip
Having Count(*) > 1

Then analyse the results.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, My answer was incomplete...

Select A.*
From [YourTable] A
, 
(
SELECT ID, City, Zip, Count(*)
FROM [YourTable]
GROUP BY ID, City, Zip
HAVING (Count(*)>1)
) B

Where A.ID=B.ID
  AND A.City=B.City
  AND A.ZIP=B.Zip

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]