Oracle, row_number, deleting rows

Question

I have a little problem. Here's a code:

  select * from
(select department_id, manager_id,last_name, salary, row_number() over
(partition by department_id,manager_id order by salary asc) p_number
from employees )
where p_number <=3;

This query shows 3 the least salaries in every department for every manager in department ex: Dep no.30 has 2 managers (100 and 114), manager no.100 has 1 employee beneath him, manager no.114 - 3 employees.

part of result:

20  100 Hartstein   13000   1
20  201 Fay         6000    1
30  100 Raphaely    11000   1
30  114 Colmenares  2500    1
30  114 Himuro      2600    2
30  114 Tobias      2800    3

Now, i want to delete all rows, where there is only 1 employee beneath manager. In this example there should be, Harstein, Fay, Raphaely. Colmenares also has number 1, but there is more employees under manager 114. Any ideas? PS Having Count is out, because there is no group by, and modifying

where p_number <=3;

into

 where p_number <=3 and p_number >1;

is also out, because it will delete all my employees with no.1 and i want to 'safe' few as they have more 'colleagues' :) Thanks!

Answer 1

This will give the result and only require a single table scan:

SELECT *
FROM   (
  SELECT department_id,
         manager_id,
         last_name, 
         salary,
         ROW_NUMBER()
           OVER ( PARTITION BY department_id, manager_id
                  ORDER BY salary ASC ) AS p_number,
         COUNT(*)
           OVER ( PARTITION BY department_id, manager_id ) AS p_count
  FROM   employees
  ORDER BY department_id, manager_id, salary
)
WHERE p_count >= 2
AND   p_number <= 3;

Output:

DEPARTMENT_ID MANAGER_ID LAST_NAME      SALARY   P_NUMBER    P_COUNT
------------- ---------- ---------- ---------- ---------- ----------
           30        114 Colmenares       2500          1          3 
           30        114 Himuro           2600          2          3 
           30        114 Tobias           2800          3          3