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?

Answer 1

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

Answer 2

Simplest I can think of:

select job_number, count(*)
from jobs
group by job_number
having count(*) > 1;

Answer 3

How about:

SELECT <column>, count(*)
FROM <table>
GROUP BY <column> HAVING COUNT(*) > 1;

To answer the example above, it would look like:

SELECT job_number, count(*)
FROM jobs
GROUP BY job_number HAVING COUNT(*) > 1;

Answer 4

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.

Answer 5

another way:

 select *
   from table a
  where exists (select 1 from table
                 where column_name=a.column_name
                   and rowid<a.rowid)

Works fine (quick enough) when there is index on column_name. And it's better way to delete or update dublicate rows.

Answer 6

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