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

User · Answer

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     gt  1   You ll need second  of nested  query to get list of login id by email

User · Answer

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  gt  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     gt  1

User · Answer

use this if your email column contains empty values    select   from table where email in       select email from table group by email having count     gt  1 and email

User · Answer

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

User · Answer

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 john123 hotmail com as a login id value

User · Answer

select email from mytable group by email having count     gt 1

User · Answer

This works best  Screenshot   SELECT RollId  count    AS c      FROM  tblstudents       GROUP BY RollId      HAVING c  gt  1      ORDER BY c DESC

User · Answer

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   gt  1

User · Answer

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     gt 1 order by C desc

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

Examples related to sql

Examples related to mysql

Examples related to database