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

All we need is an easy explanation of the problem, so here it is.

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

login_id | email
---------|---------------------
john     | [email protected]
peter    | [email protected]
johnny   | [email protected]
...

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)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This query will give you a list of email addresses and how many times they’re used, with the most used addresses first.

SELECT email,
       count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC

If you want the full rows:

select * from table where email in (
    select email from table
    group by email having count(*) > 1
)

Method 2

select email from mytable group by email having count(*) >1

Method 3

Here is query to find email‘s which are used for more then one login_id:

SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1

You’ll need second (of nested) query to get list of login_id by email.

Method 4

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

Method 5

use this if your email column contains empty values

 select * from table where email in (
    select email from table group by email having count(*) > 1 and email != ''
    )

Method 6

Thanks guys 🙂 I used the below because I only cared about those two columns and not so much about the rest. Worked great

  select email, login_id from table
    group by email, login_id
    having COUNT(email) > 1

Method 7

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 = [email protected]) 

This will return all records that have [email protected] as a login_id value.

Method 8

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 = [email protected]) 

Method 9

This works best

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

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

Method 10

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);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply