Oracle Database find duplicate records on certain column in a table.

This is a Beginner level article but can be referred readily by guys using Oracle Database moderately for a refernence.

You can identify the duplicate records in Oracle Table by using a group by clause. You need to group by column / columns from which you are trying to identify the duplicates , use count() function to identify the duplicates : expression is self exploratory having count () > 1 .


Ex 1 : Identify duplicates first names from employees table.
 Note in this context : Employee records is not necessarily duplicate we are only trying to find employees with same first name.

select empl_first_name  , count(empl_first_name)
from employees
group by empl_first_name 
having count(empl_first_name) > 1

Ex 2 : Identify duplicates or repeats based on 2 or more columns.

select empl_first_name  , empl_last_name  ,  count(empl_first_name)
from employees 
group by empl_first_name , empl_last_name
having count(1) > 1

Identify duplicate / repeats based on 2 or more columns.

Ex 3. Use of Analytic function to identify duplicates.

select * from 
(select count(1) over (partition by empl_first_name ) fname_count , e.*   
from employees e)
where fname_count > 1

Advantage in this example is you were able to select all columns in the query resultset unlike the pure group by queries above.

Ex 4. Delete duplicate rows : (Be careful and wary of what you are doing)

delete from     employees where rowid in
 (select rowid from 
   (select 
     rowid,
     row_number()
    over 
     (partition by empl_first_name order by empl_first_name) dup
    from employees)
  where dup > 1)