How to Delete Duplicate Rows in a Oracle Table

---->> ========================================================================

SELECT * FROM DEPT;

INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 20;

INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 10;

INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 30;

INSERT INTO DEPT SELECT * FROM DEPT WHERE DEPTNO = 20;

commit;

SELECT * FROM DEPT;

select * from dept group by DEPTNO, DNAME, LOC having count(*) > 1;

---->> ========================================================================

==> delete from dept where rowid in (select rowid from dept minus select max(rowid) from dept group by DEPTNO, DNAME, LOC);

==> delete from dept where rowid NOT in (select min(rowid) from dept group by DEPTNO, DNAME, LOC);

==> DELETE FROM DEPT A WHERE a.rowid > (SELECT min(B.rowid) FROM DEPT B WHERE A.DEPTNO = b.DEPTNO AND A.DNAME = B.DNAME and A.LOC=B.LOC);

==> delete from dept t1 where rowid != (select max(rowid) from dept b where b.deptno = t1.deptno and b.dname = t1.dname and b.loc=t1.loc);

==> delete from dept t1 where exists (select 'x' from dept t2 where t2.deptno = t1.deptno and t2.dname = t1.dname and t1.loc = t2.loc and t2.rowid  > t1.rowid);

==> DELETE FROM dept a WHERE (a.deptno,a.dname,a.loc) IN (SELECT b.deptno,b.dname,b.loc FROM dept b WHERE a.deptno = b.deptno AND a.dname = b.dname AND a.loc = b.loc AND a.ROWID  > b.ROWID);

==> DELETE FROM DEPT A WHERE a.rowid > ANY (SELECT B.rowid FROM DEPT B WHERE A.DEPTNO = b.DEPTNO AND A.DNAME = B.DNAME and A.LOC=B.LOC);

==> delete from dept where rowid in (select rowid from (select rowid, row_number() over (partition by deptno order by deptno) dup from dept) where dup > 1);

==> delete from dept where rowid IN (select rowid from (select rowid, row_number() over (partition by deptno,dname,loc order by rowid) rn from dept) where rn <> 1);

==> delete dept where rowid in(select lead(rowid) over (partition by deptno order by null) from dept);

==> DELETE FROM dept WHERE rowid IN

    (SELECT "rowid"

     FROM

     (SELECT "rowid", rank_n

      FROM

      (SELECT rank() over (partition BY deptno order by rowid) rank_n, rowid AS "rowid"

       FROM dept

       WHERE deptno IN (SELECT deptno FROM dept GROUP BY deptno,dname,loc HAVING COUNT(*) > 1)

      )

     )

     WHERE rank_n > 1

    );

==> DELETE FROM dept WHERE deptno in (SELECT deptno FROM dept WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM dept GROUP BY deptno));

==> delete  from dept where ('Delete',deptno) in (select case when count(*)>1 then 'Delete' else 'No' end DeleteFlag ,deptno from dept group by deptno having count(*)>1);

---->> ========================================================================