Find rows that have the same value on a column in MySQL
SqlMysqlDatabaseSql Problem Overview
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)
Sql Solutions
Solution 1 - Sql
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
)
Solution 2 - Sql
select email from mytable group by email having count(*) >1
Solution 3 - Sql
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.
Solution 4 - Sql
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
Solution 5 - Sql
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 != ''
)
Solution 6 - Sql
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
Solution 7 - Sql
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 [email protected] as a login_id value.
Solution 8 - Sql
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)
Solution 9 - Sql
This works best
> Screenshot
SELECT RollId, count(*) AS c
FROM `tblstudents`
GROUP BY RollId
HAVING c > 1
ORDER BY c DESC
Solution 10 - Sql
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);