APEX-AT-WORK no image
Tags:

How to find duplicate rows?

Von Tobias Arnhold 6.08.2011
Basically with a single table you do like this:

SELECT COLUMN_A, COLUMN_B, COUNT(*)
FROM MY_TABLE
GROUP BY COLUMN_A, COLUMN_B
HAVING COUNT(*)>1;

If you have a special select and you need to find duplicate rows then do like this:

select count(*), cust_no
(
select
c.cust_no, c.cust_name, s.order_date, s.art_no, s.art_amount
from table_sales s, table_customer c
where c.cust_no = s.cust_no
and s.country_no = 49
and s.order_date = trunc(sysdate-1)
group by
order by 2,3
)
group by cust_no
having count(*) > 1

Post Tags:

1 One Comment " How to find duplicate rows? "

llamer 09 June, 2011 05:48

Hi Tobias,
here one more example:

SELECT *
FROM (
SELECT t.*
, ROW_NUMBER() OVER(PARTITION BY t.column_a, t.column_b ORDER BY t.column_a) rn
FROM my_table t
)
WHERE rn > 1