Problem
Some rows in a [member] table have the same email column value.
login_id | email
---------|---------------------
john | john123@hotmail.com
peter | peter456@gmail.com
johnny | john123@hotmail.com
...
Because no unique constraint was specified on this column, some people used a different login id but the same email address. Now I need to locate these rows and determine whether or not they should be eliminated.
To find these rows, what SQL statement should I use? (MySQL version 5)
Asked by bobo
Solution #1
This query will return a list of email addresses along with the number of times they’ve been used, with the most frequently used emails appearing first.
SELECT email,
count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC
If you want all of the rows, click here.
select * from table where email in (
select email from table
group by email having count(*) > 1
)
Answered by Scott Saunders
Solution #2
select email from mytable group by email having count(*) >1
Answered by HLGEM
Solution #3
Here’s a query to detect emails that are associated with multiple login ids:
SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1
To receive a list of login ids by email, you’ll need the second (of nested) query.
Answered by Ivan Nevostruev
Solution #4
For MSSQL, the first part of the approved response does not work. This was effective for me:
select email, COUNT(*) as C from table
group by email having COUNT(*) >1 order by C desc
Answered by Sergey Makhonin
Solution #5
If your email column has no values, use this.
select * from table where email in (
select email from table group by email having count(*) > 1 and email != ''
)
Answered by ramesh kumar
Post is based on https://stackoverflow.com/questions/1786533/find-rows-that-have-the-same-value-on-a-column-in-mysql