sqloracle-databaseduplicate-data

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


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?


Solution

  • Aggregate the column by COUNT, then use a HAVING clause to find values that appear more than once.

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