Sql – Delete limited n rows from oracle sql table

delete-roworaclesql

I want to delete exactly 2 rows/records for each employee that is working in more than 3 projects. Let's say I have this table:

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

I can query which employees are working in more than 3 projects. In this case the employee with id 1 and the employee with id 3. The query should be:

select employee
  from (
    select employee, count(*) my_count
      from my_table
      group by employee
  ) VW
  where VW.my_count >= 3;

It is not important which rows to delete, what is relevant is to delete two rows/records for every employee that works in more than three projects.
The resulting table could be for example:

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

Best Answer

It is simple approach for this example remove 1000 first rows:

DELETE FROM YOUR_TABLE WHERE ROWID IN 
(SELECT ROWID FROM YOUR_TABLE FETCH FIRST 1000 ROWS ONLY);
Related Topic