Get Ids of rows that has duplicates in a column

Question

I have the following table users

id | phone    
--------------
1  | +1111    
2  | +2222    
3  | +3333    
4  | +4444    
5  | +1111

I need to get all ids, where users have duplicate phone. For example, the query must return the following dataset: [1,5]. Because user with id 1 has phone +1111 and user with id 5 has phone +1111.

There is something close in this answer. But I need to modify it for mysql. Currently, I have the following sql:

select  group_concat(id) as id from users
group by  phone
having count(id) > 1

But it returns a response with not wery good format. See:

Of corse, the reason is group_concat.

So, I need to get result as list of ids, not list of groups. Ho to do that for mysql?

Gordon Linoff · Answer 1 · 2020-09-02T12:56:14.810

1

You can use window functions:

select id, phone
from (select u.*, count(*) over (partition by phone) as cnt
      from users
      group by  phone
     ) u
where cnt > 1;

My recommended solution in older version is exists:

select u.*
from users u
where exists (select 1
              from users u2
              where u2.phone = u.phone and u2.id <> u.id
             );

With an index on users(phone, id), I would expect this to be the fastest solution -- even faster than the window functions approach.

edited Sep 02 '20 at 12:56

answered Sep 02 '20 at 10:54

Gordon Linoff

1,242,037
58
646
786

Thank you for fast answer but it gives me an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by phone) as cnt from users" I use 5.7 mysql – Aleksej_Shherbak Sep 02 '20 at 10:58
1

@Aleksej_Shherbak . . . You should upgrade to a more recent version of MySQL. And, if using old versions, you should tag the question correctly. But I added an alternative which I think has the best performance. – Gordon Linoff Sep 02 '20 at 12:55

score 1 · Answer 2 · answered Sep 02 '20 at 10:57

1

To complement Gordon's MySQL 8+ solution, I thought I would chime in with an approach for earlier versions of MySQL:

SELECT u1.*
FROM users u1
INNER JOIN
(
    SELECT phone
    FROM users
    GROUP BY phone
    HAVING COUNT(*) > 1
) u2
    ON u1.phone = u2.phone

answered Sep 02 '20 at 10:57

Tim Biegeleisen

502,043
27
286
360

Yes! It works for me! I just use `u1.id` instead of `u1.*` and have the list of ids. Thank you! – Aleksej_Shherbak Sep 02 '20 at 11:01

score 1 · Accepted Answer · answered Sep 02 '20 at 11:05

1

Of course, the Group Sum should be the preferred solution on MySQL 8, but this is a perfect case for a simple Subquery:

SELECT *
FROM users
WHERE phone IN
 (
   SELECT phone
   FROM users
   GROUP BY phone
   HAVING COUNT(*) > 1
 )

If you need to display the actual count, go with Tim's solution.

answered Sep 02 '20 at 11:05

dnoeth

59,503
4
39
56

Thank you! Simple and clean solution! Really good case for subquery. – Aleksej_Shherbak Sep 02 '20 at 11:11

score 0 · Answer 4 · answered Sep 02 '20 at 12:40

0

Simple solution without sub-query:

SELECT users.id, users.phone
FROM users
JOIN users phones on phones.phone = users.phone
GROUP BY users.id, users.phone
HAVING COUNT(*) > 1;

Live fiddle here

answered Sep 02 '20 at 12:40

Slava Rozhnev

9,510
6
23
39

Get Ids of rows that has duplicates in a column

4 Answers4