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

database, mysql, sql

Solution

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
)

Problem

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)

Original source

Related problems