SQL: Get duplicates in same table

Question

I have the following table:

name  email              number  type
1     abc@example.com     10     A
1     abc@example.com     10     B
2     def@def.com         20     B
3     ggg@ggg.com         30     B
1     abc@example.com     10     A
4     hhh@hhh.com         60     A

I want the following:

Result

name  email              number  type
1     abc@example.com     10     A
1     abc@example.com     10     B
1     abc@example.com     10     A

Basically, I want to find the first lines where the three columns (name, email, number) are identical and see them, regardless of type.

How can I achieve this in SQL? I don't want a result with every combination once, I want to see every line that is in the table multiple times.

I thought of doing a group by but a group by gives me only the unique combinations and every line once. I tried it with a join on the table itself but somehow it got too bloated.

Any ideas?

EDIT: I want to display the type column as well, so group by isn't working and therefore, it's not a duplicate.

Tag your question with the database you are using. – Gordon Linoff May 17 '18 at 13:56 — Gordon Linoff, May 17 '18 at 13:56

Yogesh Sharma · Answer 1 · 2018-05-17T14:04:32.977

1

You can use exists for that case :

select t.*
from table t 
where exists (select 1 
              from table 
              where name = t.name and email = t.email and 
                    number = t.number and type <> t.type);

You can also use window function if your DBMS support

select * 
from (select *, count(*) over (partition by name, email, number) Counter 
      from table
     ) t
where counter > 1;

edited May 17 '18 at 14:04

answered May 17 '18 at 13:52

Yogesh Sharma

49,870
5
26
52

Gordon Linoff · Answer 2 · 2018-05-18T03:09:48.700

1

You can use window functions:

select t.*
from (select t.*, count(*) over (partition by name, email, number) as cnt
      from t
     ) t
where cnt > 1;

If you only want combos that have different types (which might be your real problem), I would suggest exists:

select t.*
from t
where exists (select 1 
              from t t2
              where t2.name = t.name and t2.email = t.email and t2.number = t.number and t2.type <> t.type
             );

For performance, you want an index on (name, email, number, type) for this version.

edited May 18 '18 at 03:09

answered May 17 '18 at 13:55

Gordon Linoff

1,242,037
58
646
786

I tried this one and the query was just going on for too long. My table is 100.000 lines long, so maybe that is why. – OldMcDonald May 17 '18 at 14:55

score 1 · Answer 3 · answered May 17 '18 at 14:33

Core SQL-99 compliant solution.

Have a sub-query that returns name, email, number combinations having duplicates. JOIN with that result:

select t1.*
from tablename t1
join (select name, email, number
      from tablename
      group by name, email, number
      having count(*) > 1) t2
on  t1.name = t2.name
and t1.email = t2.email
and t1.number = t2.number

SQL: Get duplicates in same table

3 Answers3