How do I find duplicate values in a table in Oracle?
Aggregate the column by COUNT, then use a HAVING clause to find values that appear greater than one time.
SELECT column_name, COUNT(column_name)FROM table_nameGROUP BY column_nameHAVING COUNT(column_name) > 1;
Another way:
SELECT *FROM TABLE AWHERE 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 duplicate rows.
Simplest I can think of:
select job_number, count(*)from jobsgroup by job_numberhaving count(*) > 1;