MySQL Find rows that have the same value on a column

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.

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
)

Comments

Popular posts from this blog

Java : Variables Declaring

LINUX Move and copy files using SSH

SQL Self JOIN