SQL: Get duplicates in same table [duplicate]

Question

I have the following table:

name  email              number  type
1     [email protected]     10     A
1     [email protected]     10     B
2     [email protected]         20     B
3     [email protected]         30     B
1     [email protected]     10     A
4     [email protected]         60     A

I want the following:

Result

name  email              number  type
1     [email protected]     10     A
1     [email protected]     10     B
1     [email protected]     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
– Gordon Linoff

2018-05-17 13:56:13 +00:00
Commented May 17, 2018 at 13:56 — Gordon Linoff
– Gordon Linoff, Commented May 17, 2018 at 13:56

Yogesh Sharma · Accepted Answer · 2018-05-17 14:04:32Z

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, 2018 at 14:04

answered May 17, 2018 at 13:52

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jarlh · Accepted Answer · 2018-05-17 14:33:40Z

1

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

answered May 17, 2018 at 14:33

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-05-18 03:09:48Z

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, 2018 at 3:09

answered May 17, 2018 at 13:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

OldMcDonald Over a year ago

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.

Collectives™ on Stack Overflow

SQL: Get duplicates in same table [duplicate]

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related