Finding similar but not duplicate entries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding similar but not duplicate entries

Author

Topic

davebatt
Starting Member

17 Posts

Posted - 2010-04-26 : 12:40:42

Hello,

I did this about six months ago on one of my tables and think it may have involved joining it to itself but cant quite remember.

I have users table, basic stuff uniqueidentifier column and then stuff like email, phone, National insurance number etc.

It doesn't have duplicates (because of uniqueidentifier) column, but users may have regsitered several times.

How would I get a list of users who I think may be duplicates by looking at multiple entries for phone number, email etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 12:53:05

Here's an example based upon the Email column:

SELECT UserName, dt.Email
FROM SomeTable st
JOIN (SELECT Email, COUNT(*) FROM SomeTable GROUP BY Email HAVING COUNT(*) > 1) dt
ON st.Email = dt.Email
ORDER BY UserName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 00:38:15

if 2005, you can also use row_number() for this


SELECT email, phone, [National insurance number] ...
FROM
(SELECT COUNT(uid) OVER (PARTITION BY Username) AS occur,email, phone, [National insurance number] ...
FROM Table
)t
WHERE occur>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 03:02:41

If you use Tara' code, make a slight change

Either there should be alias for count(*) or there may not be count(*) at all as a column as it is not used in join

Madhivanan

Failing to plan is Planning to fail

davebatt
Starting Member

17 Posts

Posted - 2010-04-27 : 05:01:38

Thanks a lot for your suggestions everyone, I'll be able to use this.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources