January 19, 2011 at 11:25 am
Hi. I want to find address duplicates. ACCOUNT and ADDRESS are columns(all the addresses are formatted perfectly, don't worry about that). Take some fictitious table name(table_name). Now i want to find different ACCOUNT's living in the same ADDRESS. Please help me with the logic, with speed performance.
January 19, 2011 at 11:46 am
Read up on GROUP BY and HAVING!
But the answer is something similar to...
SELECT account
FROM yourTable
GROUP BY address
HAVING count(*) > 1
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 19, 2011 at 12:25 pm
varunkum (1/19/2011)
Hi. I want to find address duplicates. ACCOUNT and ADDRESS are columns(all the addresses are formatted perfectly, don't worry about that). Take some fictitious table name(table_name). Now i want to find different ACCOUNT's living in the same ADDRESS. Please help me with the logic, with speed performance.
Give this a try
SELECT
T1.Account
,T1.Address
FROM
Table AS T1
JOIN
(
SELECT
Address
FROM
Table
GROUP BY
Address
HAVING
SUM(1) > 1
AND MAX(Account) <> MIN(Account) ) AS DQ1
ON
T1.Address = DQ1.Address
January 19, 2011 at 1:42 pm
CELKO (1/19/2011)
De-duping address data is best done with mailing list software. Get a package. Look up the CASS Standards from USPS.and the 9-1-1 Standards for addresses.
I'll second this advice. There are even companies online that can do this service for you - I deal with one of them. Ask if you want more information.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply