Deleting all rows from database except first two rows in oracle

Question

Hello I have a problem while deleting rows , I want to keep two rows in database and delete all other rows i found a query in this link for delete row but It is gives mysql Syntax limit function. but in oracle no limit variable available . how can i do the same?

DELETE FROM Table
  WHERE DateColumn NOT IN (SELECT DateColumn 
                             FROM Table 
                         GROUP BY date(DateColumn) 
                         ORDER BY DateColumn DESC
                            LIMIT 2);

Answer 1

This cab be achieved through this query

DELETE FROM TABLE 
 WHERE ROWID NOT IN
    (SELECT ROWIDS FROM (SELECT MIN(ROWID) ROWIDS 
                           FROM TABLE 
                          GROUP BY DATECOLUMN
                          ORDER BY DATECOLUMN DESC)
      WHERE ROWNUM <= 2);

The inner most query will return min of rowid's from table and then the immediate outer query will select only two rows to be avoided in the outermost delete.

Answer 2

Use ROWID, ROWNUM and subquery something like this (not tested)

delete from table WHERE rowid NOT IN
    (SELECT rowid FROM (SELECT rowid
                          FROM Table 
                         ORDER BY DateColumn DESC)
     WHERE rownum <= 2);

Notice the "double" select is needed because rownum where messes with the order by as it is applied after the rownum

If the dates don't matter and you just want to keep two rows (random) it can be simplified to

delete from table WHERE rowid NOT IN
    (SELECT rowid FROM table WHERE rownum <= 2);