How do I find duplicate values in a table in Oracl

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?

Answer 1

SELECT   SocialSecurity_Number, Count(*) no_of_rows
FROM     SocialSecurity 
GROUP BY SocialSecurity_Number
HAVING   Count(*) > 1
Order by Count(*) desc 

Answer 2

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 < 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) > 1;

Answer 3

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Answer 4

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(*) > 1)
order by column_name desc;

also can add some additional filters in the where clause if needed.

Answer 5

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

Answer 6

Here is an SQL request to do that:

select column_name, count(1)
from table
group by column_name
having count (column_name) > 1;