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

Question

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

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

Some people used 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)

Answer 1

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

Answer 2

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.

Answer 3

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
)