Reputation: 71
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
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
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
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