How to find duplicates in a table?
select column from table group by column having count(*)>1
CREATE TABLE <sample table>; ALTER TABLE <sample table> ADD (SELECT DISTINCT * FROM <table>); DELETE FROM <table> WHERE (SELECT * FROM <sample table>); Back up the unique entries into another table and delete them from the old one. What you'll have left is the duplicates. Test this on a dummy table first as I can't in this moment.
question was to find the duplicates not remove them
select * from dept where rowid not in (select max(rowid) from dept group by deptno,dname,loc); the above will show you all duplicate excluding unique in oracle database Scott schema table dept
Select count(column) from table group by column
@Manish didn't quite get you.. what was the question? To remove duplicates you should be using DISTINCT