SQL >> delete the Duplicates

oracle (SQL) query to find the duplicate records in a oracle data base.
(the question as to why your primary key constraint didn't kick out the duplicate entries!)

select * from table group by key1, key2, ..., keyN having count (*) > 1);

delete from ( select * from table group by key1, key2, ..., keyN having count (*) > 1);
If you want to list them for a data cleanup excercise add an order by clause to list them in the order you want.

**************************************
U can find and delete the Duplicate files

Delete from test a where rowid <> ( select max(rowid)
from test b where a.sno = b.sno and a.sname = b.sname )

This querry will find duplicate rows and delete those.

*************************************

DELETE FROM ssrtable WHERE rowid NOT IN (SELECT max(rowid) FROM ssrtable GROUP BY id);