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:
|
1 |
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:
|
1 |
select t1.FullName from Users as t1 GROUP by FullName having COUNT (FullName) > 1 |
Insert this SQL to WHERE IN () condition:
|
1 2 |
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.
Conditions IF ELSE in Transact SQL
Nice way to select all the child elements in SQL