How do I find duplicate values in a table in Oracle

Question

What s the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table   For example  I have a JOBS table with the column JOB NUMBER   How can I find out if I have any duplicate JOB NUMBERs  and how many times they re duplicated

User · Answer

In case where multiple columns identify unique row (e.g relations table ) there you can use following

Use row id e.g. emp_dept(empid, deptid, startdate, enddate) suppose empid and deptid are unique and identify row in that case

select oed.empid, count(oed.empid) 
from emp_dept oed 
where exists ( select * 
               from  emp_dept ied 
                where oed.rowid <> ied.rowid and 
                       ied.empid = oed.empid and 
                      ied.deptid = oed.deptid )  
        group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);

and if such table has primary key then use primary key instead of rowid, e.g id is pk then

select oed.empid, count(oed.empid) 
from emp_dept oed 
where exists ( select * 
               from  emp_dept ied 
                where oed.id <> ied.id and 
                       ied.empid = oed.empid and 
                      ied.deptid = oed.deptid )  
        group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);

User · Answer

Another way   SELECT   FROM TABLE A WHERE EXISTS     SELECT 1 FROM TABLE   WHERE COLUMN NAME   A COLUMN NAME   AND ROWID  lt  A ROWID     Works fine  quick enough  when there is index on column name  And it s better way to delete or update duplicate rows

User · Answer

I usually use Oracle Analytic function ROW NUMBER     Say you want to check the duplicates you have regarding a unique index or primary key built on columns  c1  c2  c3    Then you will go this way  bringing up ROWID s of rows where the number of lines brought by ROW NUMBER   is  gt 1   Select   From Table With Duplicates       Where Rowid In                      Select Rowid                        From  Select Rowid                                      ROW NUMBER   Over                                               Partition By c1    c2    c3                                             Order By c1    c2    c3                                           nbLines                                From Table With Duplicates  t2                       Where nbLines  gt  1

User · Answer

How about   SELECT  lt column gt   count    FROM  lt table gt  GROUP BY  lt column gt  HAVING COUNT     gt  1    To answer the example above  it would look like   SELECT job number  count    FROM jobs GROUP BY job number HAVING COUNT     gt  1

User · Answer

SELECT   SocialSecurity Number  Count    no of rows FROM     SocialSecurity  GROUP BY SocialSecurity Number HAVING   Count     gt  1 Order by Count    desc

User · Answer

You don t need to even have the count in the returned columns if you don t need to know the actual number of duplicates  e g   SELECT column name FROM table GROUP BY column name HAVING COUNT     gt  1

User · Answer

Also u can try something like this to list all duplicate values in  a table say reqitem  SELECT count poid   FROM poitem  WHERE poid   50  AND rownum  lt  any  SELECT count     FROM poitem WHERE poid   50   GROUP BY poid  MINUS SELECT count poid   FROM poitem  WHERE poid in  50  GROUP BY poid  HAVING count poid   gt  1

User · Answer

Here is an SQL request to do that   select column name  count 1  from table group by column name having count  column name   gt  1

User · Answer

I know its an old thread but this may help some one   If you need to print other columns of the table while checking for duplicate use below   select   from table where column name in  select ing column name from table ing group by ing column name having count     gt  1  order by column name desc    also can add some additional filters in the where clause if needed

User · Answer

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   gt  1

User · Answer

Simplest I can think of   select job number  count    from jobs group by job number having count     gt  1

User · Answer

Doing  select count j1 job number   j1 job number  j1 id  j2 id from   jobs j1 join jobs j2 on  j1 job numer   j2 job number  where  j1 id    j2 id group by j1 job number   will give you the duplicated rows  ids

User · Answer

1  solution  select   from emp     where rowid not in      select max rowid  from emp group by empno

[sql] How do I find duplicate values in a table in Oracle?

Examples related to sql

Examples related to oracle

Examples related to duplicate-data