Monday, June 30, 2008

Finding duplicate rows in Oracle

Adding these to my utility sqls.

SELECT col1, col2, COUNT(*)
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1

SELECT *
FROM table1 a
WHERE ROWID !=
(SELECT MAX(ROWID) FROM table1 b
WHERE a.col1 = b.col1
AND a.col2 = b.col2)

Note: col1 and col2 are assumed to be the unique columns that identify a row.

3 comments:

Anonymous said...

Hi

oracle ebs r12 said...

Great post. Thanks for sharing the whole query to perform this task. I was trying to do the same thing but is not getting the exact answers. I actually made a query the other way and when I studied the query shared by you I realized my error. Thanks again for solving my problem.

Sujay said...

Glad to know you found this useful.