Oracle DB how to delete all duplicates but one

Question

We have an Oracle DB with a table with n duplicated records, as in:

select * from benefit_allowed_tiers:

ben_tier_id   ben_id  ben_tier_alowed   name
1202932929    33817   1202932929        £10,000
1202932934    33817   1202932934        £30,000
1202932931    33817   1202932931        £20,000
1202932925    33817   1202932925        Not selected
1202932931    33817   1202932931        £20,000 
1202932929    33817   1202932929        £10,000 
1202932934    33817   1202932934        £30,000 
1202932925    33817   1202932925        Not selected
    ... (sometimes there may be more than 2 duplicates)

I need to delete all but one of these duplicate records. How that could be achieved?

There are quite a lot of information on how to do that in other DBs, but I did not find a way how to do that in Oracle

Answer 1

Use exist as following:

Delete from benefit_allowed_tiers t
Where exists ( select 1 from benefit_allowed_tiers t1
Where t1.rowid > t.rowid
And t1.ben_tier_id = t.ben_tier_id
And t1.ben_id = t.ben_id
And t1.ben_tier_alowed = t.ben_tier_alowed
And t1.name = t.name);

Or use analytical function as following:

Delete from benefit_allowed_tiers
Where rowid in
(Select rid from
  (Select t.rowid rid, 
          row_number() over (partition by ben_tier_id, ben_id, ben_tier_alowed, name order by 1) as rn
     From benefit_allowed_tiers t
  )
Where rn > 1
)

Cheers!!