I want to keep the last 20 records in VMR table and delete all other records. The VMR table has 5000000 records and its growing. I also have create_date column which has date datatype in VMR table and it has non unique index. I tried using rownum and WITH clause to delete the records and keep the last 20 records using below query but its givign an error as
ORA-00928: missing SELECT keyword
Below is the query i used :
WITH CTE AS (
SELECT t.*,
ROW_NUMBER() OVER(ORDER BY create_date DESC) as rnk
FROM VMR t)
DELETE FROM CTE
WHERE CTE.rnk > 20;