Find rows that have the same value on a column in MySQL

user_182349 0
Viewed 535196

In a [member] table, some rows have the same value for the email column.

login_id | email
---------|---------------------
john     | john123@hotmail.com
peter    | peter456@gmail.com
johnny   | john123@hotmail.com
...

Some people used a different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be removed.

What SQL statement should I use to find these rows? (MySQL 5)

mysql sql database
10 Answers

First part of accepted answer does not work for MSSQL.
This worked for me:

select email, COUNT(*) as C from table 
group by email having COUNT(*) >1 order by C desc
user_18526440

I know this is a very old question but this is more for someone else who might have the same problem and I think this is more accurate to what was wanted. 

SELECT * FROM member WHERE email = (Select email From member Where login_id = john123@hotmail.com)

This will return all records that have john123@hotmail.com as a login_id value.

user_101001360

Get the entire record as you want using the condition with inner select query.

SELECT *
FROM   member
WHERE  email IN (SELECT email
                 FROM   member
                 WHERE  login_id = abcd.user@hotmail.com)
user_111702090

This works best

Screenshot enter image description here

SELECT RollId, count(*) AS c 
    FROM `tblstudents` 
    GROUP BY RollId 
    HAVING c > 1 
    ORDER BY c DESC
user_106726760

Very late to this thread, but I had a similar situation and the following worked on MySQL. The following query will also return all the rows that match the condition of duplicate emails

SELECT * FROM TABLE WHERE EMAIL IN 
       (SELECT * FROM 
            (SELECT EMAIL FROM TABLE GROUP BY EMAIL HAVING COUNT(EMAIL) > 1) 
        AS X);
user_32149880
Related