Delete all but some rows - Oracle

oracle, sql

Solution

This will delete all rows for each unique combination of col1 and col2 other than the first five ordered by rowid

delete from my_table
where rowid in
  (
  select rowid
  from
    (
    select rowid,
           row_number() over (partition by col1, col2 order by rowid) rownumber
    from   my_table
    )
  where rownumber > 5
  )
/

Problem

I want to delete set of the rows from a table. I can decide which rows need to be deleted. I will delete the rows from table only if the count of rows is more than 5 (based on condition). Consider this sample data ``` ID--Col1--Col2-- 1 A X 2 A X 3 A X 4 A X 5 A X 6 A X 7 A X 8 A X 9 A X 10 A X 11 B X 12 B X 13 B X 14 B X 15 C X 16 C X 17 C X 18 D X 19 D X ``` I want to delete 5 Rows of {*, A, X}, I need to keep 5 of them (no matter which one). I wont delete B, C & D since count of them is less then 5. Like ``` delete from tableA --- I can decide on the rows to delete based on two conditions. where col1 = someCondition and col2 = someOtherCondition and rownum > 5 --- This dint work. I checked. ``` I think perhaps I need to programmaticaly. Any suggestions are greatly appreciated.

Original source