It’s a little tutorial about how to get all duplicate records on namesakes example from your database table. For example, we have a table with columns Id, FullName, Birthdate and Phone.

Using 'GROUP BY' and 'HAVING' we can select all namesakes. 

This query returns all the unique FullName records from a table:

select t1.FullName from Users as t1 GROUP by FullName

 

If we modify it a little, we’ll get only those FullName values, that are written more than once in a datatable:

select t1.FullName from Users as t1 GROUP by FullName having COUNT (FullName) > 1

 

Insert this SQL to WHERE IN () condition:

SELECT t1.Id, t1.FullName, t1.Phone, t1.Birthday from Users as t1 
        where FullName IN (select t1.FullName from Users as t1 GROUP by FullName having COUNT(FullName) > 1)

 

And you get the data about users with same names.

 

Homework: how to find records that are 3 or more times in table? What should you modify in these queries? Write in comments.

Yandex.RTB R-A-454402-1