CODEWITHSUNDEEP
sqloracle-database
Ajinkya Pujari
Ajinkya Pujari

Reputation: 71

How to SELECT all occurrences of all duplicate rows with all columns from a table?

For example:

select c1, c2
from mytable
group by c1, c2
having count(*) > 1;

will give only 1 copy each of the duplicates.

Upvotes: 3

Views: 2292

Answers (3)

Junaid Farooq
Junaid Farooq

Reputation: 92

Try this: select * from mytable mt join (select c1, c2 from mytable group by c1, c2 having count(*) > 1) m on mt.c1=m.c1 and mt.c2 = m.c2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use window functions:

select t.*
from (select t.*, count(*) over (partition by c1, c2) as cnt
      from mytable
     ) t
where cnt > 1;

Note that this works even when c1 and/or c2 are NULL.

However, if you only care about c1 and c2, then perhaps including the count in the result set is sufficient:

select c1, c2, count(*)
from mytable
group by c1, c2
having count(*) > 1;

Another fun method in Oracle:

select t.*
from mytable t
where exists (select 1
              from mytable t2
              where t2.c1 = t.c1 and t2.c2 = t.c2 and t2.rowid <> t.rowid
             );

However, this fails if either c1 or c2 is NULL, so the first method is more general.

Upvotes: 2

Ori Marko
Ori Marko

Reputation: 58892

add SQL with all c1/c2 values

with dup as (
select c1,c2 from mytable group by c1,c2 having count(*) > 1
)
select c1,c2 from mytable m, dup where m.c1=dup.c1 and m.c2 = c2.dup;

Upvotes: 1

Related Questions