SQL to find duplicates

Question

SQL to find duplicates

varunkum

Default port

Points: 1481
More actions
January 4, 2011 at 2:42 pm

#230015

I have a table with Names and Address fields. I need sql to find different people at same address.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 1

Use a subquery to count the occurrence of Address fields and link that back to your original table for Address values with count > 1.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

something like this:

select *

from namestable n 1

where exists (select * from namestable n2 where n2.name = n1.name and n2.address <> n1.address)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Not so simple if you have addresses like

2 South Wood Street

2 S. Wood Street

2. South Wood St.

etc, etc.

Can you supply some sample data, table definition and desired results following the outline given when you click on the first link in my signature block. Would assist in giving you a tested andswer.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

varunkum Default port Points: 1481 More actions · Answer 4

bitbucket-25253 (1/4/2011)
Not so simple if you have addresses like
2 South Wood Street
2 S. Wood Street
2. South Wood St.
etc, etc.
Can you supply some sample data, table definition and desired results following the outline given when you click on the first link in my signature block. Would assist in giving you a tested andswer.

Thanks all. @bit bucket its not that complicated. Just looking for the exact street name match. Thank you.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Sort of pseudo code - with guessed table/column names...

;with grouped_addresses as

(

select name,address1,address2,address3,sum(1) over(partition by address1 [,address2] [,address3]) as dupe_count

from addresses

)

select name,address1,address2,address3

from grouped_addresses

where dupe_count>1

If you really only care about address1, just use that in the OVER clause, otherwise you might want to include other address fields ("1 main street" could be quite a common address) to avoid false positives...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

psingla Hall of Fame Points: 3840 More actions · Answer 6

Try out this on as well..

SELECT name,

address

FROM tab tab1

WHERE EXISTS(SELECT tab2.address

FROM tab tab2

WHERE tab2.address = tab1.address

GROUP BY tab2.address

HAVING Count(1) > 1)

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/