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