Oraclesteps

• Sub-query - The inner query execute first, the outer query deprnds on inner query output.
• Correlated sub-query - The outer query execute first, the inner query deprnds on outer query result.

• A correlated sub-query is where the sub-query part makes reference to values from the outer query.

• Is not a correlated sub-query because the sub-query makes no reference to the values of the outer part of the query.

Use rowid pseudo column. All you have to do is to keep the latest data (i.e. highest ROWID) and remove other duplicated rows.
SELECT * FROM table1 a
WHERE rowid < (SELECT max(rowid) FROM table1 b
WHERE a.column1 = b.column1 AND etc...);


OR


create table testtt (num number);

insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(222);
insert into testtt values(222);
insert into testtt values(333);
insert into testtt values(333);
insert into testtt values(333);

select * from testtt;

delete from testtt
where (rowid, num) not in (select max_rid, num
from (select num,
count(num) over (partition by num) cnt,
max(rowid) over (partition by num) max_rid
from testtt)
where cnt > 1);

select * from testtt;


OR



While I doubt this method has any advantages over another, it's an example:
DELETE FROM table_a
WHERE rowid IN
( SELECT rowid FROM table_a
MINUS
SELECT MAX( rowid ) FROM table_a
GROUP BY column_list )


OR


delete from table_name where rowid not in (select max(rowid) from table group byduplicate_values_field_name);


OR


highest rowid does not necessarily mean latest data... since space freed from deleting rows might be reused.

SQL> CREATE TABLE t AS SELECT level l FROM DUAL CONNECT BY LEVEL <= 5000;

SQL> DELETE FROM t WHERE l < 5000;

SQL> COMMIT;

SQL> INSERT INTO t VALUES (5001);

SQL> COMMIT;

SQL> SELECT max(l) KEEP(DENSE_RANK LAST ORDER BY rowid) as maxrid, max(l) KEEP(DENSE_RANK FIRST ORDER BY rowid) minrid FROM t;

MAXRID MINRID
---------- ----------
5000 5001