How to Find Duplicate Values in a Table in Oracle

How do I find duplicate values in a table in Oracle?

Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.

SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;

How to find duplicate records in oracle CASE WHEN Statement

Here's one approach:

WITH x
     AS (SELECT ROW_NUMBER()
                  OVER (
                    PARTITION BY CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END
                    ORDER BY CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END) AS rn,
                CASE WHEN a.trans_id IS NULL THEN b.trans_id ELSE a.trans_id END AS trans_id,
                CASE WHEN a.trans_id IS NULL THEN b.phonenumber ELSE a.phonenumber END AS phonenumber,
                CASE
                     WHEN a.trans_id IS NULL THEN 'NOT IN TABLEA'
                     WHEN b.trans_id IS NULL THEN 'NOT IN TABLEB'
                     WHEN DECODE(a.phonenumber, b.phonenumber, 1, 0) = 0 THEN 'PHONENUMBER MISMATCH'
                END AS status
         FROM   tablea a
                FULL OUTER JOIN tableb b
                             ON a.trans_id = b.trans_id),
y AS (
SELECT trans_id,
       phonenumber,
       CASE WHEN rn > 1 THEN 'DUPLICATE RECORD' ELSE status END AS status
FROM   x)
SELECT * FROM y
WHERE  status IS NOT NULL -- Exclude TableA records that are (presumably) OK

EDIT: Adjusted query to use second CTE (y) as NOT NULL filter on status was removing rows needed for the ROW_NUMBER() check.

Find duplicate values in an Oracle table

I didn't quite understand which of these two you would need so I included both:

A query to find duplicates:

Select localid, applicationname, count(*)
from yourtable
group by localid, applicationname
having count(*) > 1;

A query to find all distinct combinations or the two fields.

select distinct localid, applicationname 
from yourtable

EDIT I

I think I now understood.
This query will give you the localids that are allocated to more than one applicationname.

select localid, count(*)
from (
    select distinct localid, applicationname 
    from yourtable
) 
group by localid
having count(*) > 1;

Oracle SQL How to find duplicate values in different columns?

Something like this... note that in the lateral clause we still need to unpivot, but that is one row at a time - resulting in possibly much faster execution than simple unpivot and standard aggregation.

with
     input_data ( id, col1, col2, col3 ) as (
       select 81, 101, 102, 101 from dual union all
       select 82, 101, 103, 104 from dual
     )
-- End of simulated input data (for testing purposes only).
-- Solution (SQL query) begins BELOW THIS LINE.
select i.id, i.col1, i.col2, i.col3, l.duplicates
from   input_data i,
         lateral ( select  case when count (distinct val) = count(val) 
                                then 'NO' else 'YES'
                           end  as duplicates
                   from    input_data
                   unpivot ( val for col in ( col1, col2, col3 ) )
                   where   id = i.id
                 ) l
;

ID  COL1  COL2  COL3  DUPLICATES
--  ----  ----  ----  ----------
81   101   102   101  YES
82   101   103   104  NO 

Find duplicate values in oracle

Your query should be

SELECT * FROM (
select col1, 
col2, 
count(col1) over (partition by col1) col1_cnt
from table1 
)
WHERE col1_cnt > 1 
order by 2 desc;

---

Related Topics