Monday, May 2, 2011

How to Delete duplicate rows AND count Duplicate Rows in table?

CREATE TABLE dupli(sname varchar2(10),sno number(5));

SNAME SNO
---------- ----
santhos 10
suresh 20
suresh 10
murali 30
murali 20
suresh 20

5 row selected

DELETE FROM dupli a where rowid <>(SELECT max(rowid) FROM dupli b where a.sno=b.sno);

3 rows deleted

SNAME SNO
---------- -------
santhos 10
murali 30
suresh 20

3 rows selected

count Duplicate rows
select count(*),sno from dupli having count(*)>1 group by sno

COUNT(*) SNO
--------- ------
3 20
2 10

2 rows selected

No comments:

Post a Comment