GuilinDev

Lc0182

05 August 2008

182 Duplicate Emails

Write a SQL query to find all duplicate emails in a table named 

1

Person
.

1
2
3
4
5
6
7

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

1
2
3
4
5

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

Schema

1
2
3
4
5

Create table If Not Exists Person (Id int, Email varchar(255))
Truncate table Person
insert into Person (Id, Email) values ('1', 'a@b.com')
insert into Person (Id, Email) values ('2', 'c@d.com')
insert into Person (Id, Email) values ('3', 'a@b.com')

Solution

1
2
3
4

Select Email
From Person
Group by Email
having count(*) > 1

1
2
3
4
5
6
7

Select Email from Person Where Id not in (
    Select tmp.Id from (
        Select min(Id) as Id 
        from Person 
            Group By email
    ) tmp
)
This site is open source. Improve this page.