SQL to delete the oldest records in a table

I'm looking for a single SQL query to run on an oracle table that will retain n number of records in a 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 appears that开发者_如何学Go I cannot have order by in the nested select.

Any help appreciated

When you use ORDER BY with ROWNUM the ROWNUM is applied first, so you don't get the results you expect. You could modify 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 will be deleted then maybe this will be faster:

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