Oracle Dense Rank

Question

I have a requirement to delete duplicate rows based on ID and times column.I need to keep records with latest times only.If there are two records with max times,I can keep anyone of the record and delete all other records within that group.Please find my input data below

ID  TIMES
123 13/01/2018
123 14/01/2018
123 15/01/2018
345 14/01/2018
567 20/01/2018
567 20/01/2018
879 NULL
879 21/01/2018

I have written a query for the same.But its not working with the case of ID=567 as both of them have same value in times column. Please find my query below

delete FROM table where (ID,times) in( 
  SELECT ID,times, 
    RANK() OVER (PARTITION BY ID ORDER BY times DESC NULLS LAST) dest_rank
    FROM TABLE 
  ) WHERE dest_rank <> 1

Is there any way I can achieve this.

Answer 1

Here is one method:

delete t from t
    where rowid <> (select max(rowid) keep (dense_rank first order by times desc)
                    from t t2
                    where t2.id = t.id
                   );

However, I would do this with a temporary table:

create temporary table tt
    select id, max(times) as times
    from t
    group by id;

truncate table t;

insert into t(id, times)
    select id, times
    from tt;