SQL to delete the oldest records in a table

Question

I am looking for one SQL query to run in an oracle table that will save n number of records in the table and delete the rest

I tried the following

delete from myTable where pk not in (SELECT pk FROM myTable where rownum <5 order by created DESC) 

But it looks like I cannot have order by in the nested element.

Any help appreciated

Answer 1

When you use ORDER BY with ROWNUM, ROWNUM is applied first, so you will not get the expected results. You can change your SQL to:

 delete from myTable where pk not in ( SELECT pk FROM ( SELECT pk FROM myTable order by created DESC) where rownum <5 ) 

There are many other ways to write this. If the table is large and most rows are deleted, it may be faster:

 delete from myTable where created < ( SELECT MIN(created) FROM ( SELECT created FROM myTable order by created DESC) where rownum <5 )