+ 1

How to find duplicates in a table?

19th Jul 2016, 9:42 AM
6 Answers
+ 1
select column from table group by column having count(*)>1
19th Jul 2016, 4:45 PM
manish rawat
manish rawat - avatar
+ 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.
20th Jul 2016, 6:15 AM
blabla blabla
blabla blabla - avatar
question was to find the duplicates not remove them
19th Jul 2016, 6:22 PM
manish rawat
manish rawat - avatar
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
19th Jul 2016, 6:43 PM
Saurabh Joshi
Saurabh Joshi - avatar
Select count(column) from table group by column
21st Jul 2016, 6:41 PM
ascetic - avatar
- 1
@Manish didn't quite get you.. what was the question? To remove duplicates you should be using DISTINCT
19th Jul 2016, 6:12 PM
harish gupta
harish gupta - avatar