Problem
What is the simplest SQL query that will provide the duplicate values for a specific field in an Oracle database table, as well as the count of their occurrences?
For instance, I have a JOBS table with the JOB NUMBER field. How can I tell if I have any duplicate JOB NUMBERs and how many of them there are?
Asked by Andrew
Solution #1
Use a HAVING clause to detect values that appear more than once in the column after aggregating it by COUNT.
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
Answered by Bill the Lizard
Solution #2
Another way:
SELECT *
FROM TABLE A
WHERE EXISTS (
SELECT 1 FROM TABLE
WHERE COLUMN_NAME = A.COLUMN_NAME
AND ROWID < A.ROWID
)
When there is an index on column name, it works perfectly (quickly enough). It’s also a better technique to get rid of or update duplicate rows.
Answered by Grrey
Solution #3
I’ll give you the simplest example I can think of:
select job_number, count(*)
from jobs
group by job_number
having count(*) > 1;
Answered by JosephStyons
Solution #4
If you don’t need to know the exact number of duplicates, you don’t even need the count in the returned columns. e.g.
SELECT column_name
FROM table
GROUP BY column_name
HAVING COUNT(*) > 1
Answered by Evan
Solution #5
How about:
SELECT <column>, count(*)
FROM <table>
GROUP BY <column> HAVING COUNT(*) > 1;
To respond to the preceding example, it would be as follows:
SELECT job_number, count(*)
FROM jobs
GROUP BY job_number HAVING COUNT(*) > 1;
Answered by Andrew
Post is based on https://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle