CODEWITHSUNDEEP
sqlsql-server
edcoder
edcoder

Reputation: 533

MSSQL: Search for duplicates in columns based on criteria

I am trying to create a script that will look through the sql columns and search for duplicates based on first name, last name, and date of birth. Can you advice me how I can start this? Like I can do it for one id, but I need to go through the enter list of ids and do the search

id    Forename    Surname    DateofBirth
1     John          Doe      2015-05-16
2     Martin        Rocks    2015-04-18
3     John          Doe      2015-05-16
4     Ben           Dover    2014-08-09

So in this case, I just want to write a script that can take each ID and look for duplicates based on matching Forename, Surname and Date of birth

Upvotes: 0

Views: 44

Answers (3)

irakliG.
irakliG.

Reputation: 176

you can use analytical function to achieve your goal like this:

select * from (
select ROW_NUMBER() OVER(PARTITION BY Forename, Surname, DateofBirth order by ID) RN,
   Forename,
   Surname,
   DateofBirth
from table_name) x
where x.rn>1

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use exists :

select t.*
from table t
where exists (select 1  
              from table t1 
              where t1.Forename = t.Forename and t1.Surname = t.Surname and 
                    t1.DateofBirth = t.DateofBirth and t1.id <> t.id
             )

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use corelated subquery

select t.* from table_name t where 
exists ( select 1 from table_name t1 where t1.DateofBirth=t.DateofBirth and t1.Forename=t.Forename
          and t1.surname=t.surname 
           group by t1.DateofBirth,t1.Forename,t1.Surname
           having count(*)>1 )

Upvotes: 1

Related Questions