Delete limited n rows from oracle sql table -

i want delete 2 rows/records each employee working in more 3 projects. let's have table:

+----------+-------------+ | employee |  project    | +----------+-------------+ |   1      |   p1        | |   1      |   p2        | |   1      |   p3        | |   1      |   p4        | |   2      |   p1        | |   2      |   p3        | |   3      |   p1        | |   3      |   p4        | |   3      |   p5        | +----------+-------------+ 

i can query witch employees working in more 3 projects. in case employee id 1 , employee id 3. query should be:

select employee   (     select employee, count(*) my_count       my_table       group employee   ) vw   vw.my_count >= 3; 

it not important rows delete, relevant delete 2 rows/records every employee works in more 3 projects. resulting table example:

+----------+-------------+ | employee |  project    | +----------+-------------+ |   1      |   p1        | |   1      |   p2        | |   2      |   p1        | |   2      |   p3        | |   3      |   p1        | +----------+-------------+ 

sql> select * t;    employee pr                                                                    ---------- --                                                                             1 p1                                                                             1 p2                                                                             1 p3                                                                             1 p4                                                                             2 p1                                                                             2 p3                                                                             3 p1                                                                             3 p4                                                                             3 p5                                                                     sql> delete t   2  rowid in (   3   select rid (   4          select rowid rid,   5          row_number() over(partition employee   6          order project desc) rn,   7          count(*) over(partition employee) cnt   8          t   9   ) cnt >= 3 , rn <=2  10  )  11  /  4 rows deleted.  sql> select * t;    employee pr                                                                    ---------- --                                                                             1 p1                                                                             1 p2                                                                             2 p1                                                                             2 p3                                                                             3 p1