Saturday, July 29, 2006

How to delete duplicate rows using analytic functions?

delete from emp
where rowid in
(select rid
from (select rowid rid, row_number() over
( partition by ENAME order by rowid ) rn from emp
)
where rn <> 1
);

This is much more fast and efficient way of removing duplicates compared to
standard approach.

No comments: