How to find duplicate rows?

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: