How to select all duplicates from Transact-SQL table on namesakes example
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.