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)

score 449 · Accepted Answer · edited Jul 20 '18 at 05:29

449

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
)

edited Jul 20 '18 at 05:29

Pablo Bianchi

1,824
1
26
30

answered Nov 23 '09 at 22:38

Scott Saunders

29,840
14
57
64

3

`count(1)` works equally well, and is more performant. (Learned that trick from [so] ;-) – jpaugh Jul 18 '16 at 18:32
5

@jpaugh, might not want to use `count(1)` https://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better – Storm Jun 06 '17 at 07:39
created what was essentially infinite recursion or something on mysql resulting in a dead database due to "too many connections" :-/ – huygir Mar 05 '18 at 17:40
Worked for SQLite as well. Thank you! – vss Aug 13 '21 at 11:05
1

@jpaugh nowadays no performance difference between `count(*)` and `count(1)`, query analyzers of DBMS are pretty smart and not going to load whole row to execute `count(*)`. Test it yourself (and don't forget to disable cache before test). – kdmitry Dec 25 '22 at 18:27
@kdmitry I'll take your word for it. Honestly, it was always bananas to me that query analyzers _couldn't_ always do that. It seems like low-hanging fruit, – jpaugh Jan 17 '23 at 18:29

score 79 · Answer 2 · answered Nov 23 '09 at 22:36

79

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

answered Nov 23 '09 at 22:36

HLGEM

94,695
15
113
186

18

The accepted answer doesn't work with Postgres, this one does. – azio Dec 11 '12 at 02:33
@HLGEM: http://stackoverflow.com/questions/41359879/postgresql-get-records-having-similar-column-values – Syed Asad Abbas Zaidi Dec 28 '16 at 11:01

score 16 · Answer 3 · answered Nov 23 '09 at 22:36

16

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.

answered Nov 23 '09 at 22:36

Ivan Nevostruev

28,143
8
66
82

score 12 · Answer 4 · answered Nov 08 '13 at 09:27

12

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

answered Nov 08 '13 at 09:27

Sergey Makhonin

350
3
13

score 6 · Answer 5 · answered May 17 '16 at 17:26

6

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 != ''
    )

answered May 17 '16 at 17:26

ramesh kumar

81
1
2

score 3 · Answer 6 · answered Feb 11 '16 at 09:21

3

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

answered Feb 11 '16 at 09:21

Libertine Makinta

403
5
10

2

In the case in question, COUNT(email) would always be 1, so you query will return nothing. – jutky Jan 23 '17 at 19:49
1

No, the query actually gave me the data I needed, which is distinctly the email and login_name of those who have the same email – Libertine Makinta Jun 20 '19 at 13:38
1

If you group by email *and* login_id, you will count amount of rows for same email and login, and those are distinct in your example, so count will be always be 1. Here is the fiddle with your query that returns 0 rows: http://sqlfiddle.com/#!9/4bbcaf/3 – jutky Jun 22 '19 at 20:36

score 3 · Answer 7 · answered Jul 18 '18 at 14:19

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.

score 3 · Answer 8 · edited Dec 16 '20 at 17:43

3

This works best

Screenshot

SELECT RollId, count(*) AS c 
    FROM `tblstudents` 
    GROUP BY RollId 
    HAVING c > 1 
    ORDER BY c DESC

edited Dec 16 '20 at 17:43

marcogmonteiro

2,061
1
17
26

answered Dec 16 '20 at 14:18

Adeleye Ayodeji

325
3
5

score 2 · Answer 9 · edited Mar 08 '19 at 10:31

2

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)

edited Mar 08 '19 at 10:31

karel

5,489
46
45
50

answered Mar 08 '19 at 10:23

Suba Karthikeyan

21
1

score 0 · Answer 10 · answered Dec 28 '21 at 01:04

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

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

10 Answers10

Linked