I want to write an oracle sql query to keep first three latest records ordered by TIMESTAMP and delete the rest for each MACHINE_ID. I want to know how efficient i can do that. Hope you understand my question!!

Below is the table for example. All the records with USERFILE = 0 can be filtered out in the sql query.

**Result after - group by MACHINE_ID and sort by TIMESTAMP desc **

After leaving the first 3 latest records per MACHINE_ID and deleting the oldest records, final result should be

CodePudding user response：

You can number the rows per machine and then delete all rows with a number greater than 3. Ideally we could simply delete from a query, but I'm getting ORA-01732: data manipulation operation not legal on this view when trying this in Oracle 19c.

We need two steps hence:

1. find the rows
2. delete the rows

The statement using rowid to acces the rows again quickly:

delete from mytable
where rowid in
(
  select rowid
  from 
  (
    select
      rowid,
      row_number() over (partition by machine_id order by timestamp desc) as rn
    from mytable
  )
  where rn > 3
);

CodePudding user response：

One method is:

delete from t
    where t.timestamp not in (select t2.timestamp
                              from t t2
                              where t2.machine_id = t.machine_id 
                              order by t2.timestamp desc
                              fetch first 3 rows only
                             );

For performance, you want an index on (machine_id, timestamp desc).