How can I find the first 5 highest salaried employees in each dept in an Oracle Database (not using TOP)?
This question is related to a previous question, How to find the first 5 highest salaried employees in each department (25 April 2001). Using the TOP keyword, the answer was --
select * from employee X where salary in (select top 5 salary from employee where dept = X.dept order by salary desc) order by dept, salary desc
Without using the TOP keyword, we resort to the more generic rank subquery --
select * from employee X where 5 > (select count(*) from employee where dept = X.dept and salary > X.salary) order by dept, salary desc
The subquery counts the number of rows in the same department that have a salary greater than the salary of the row under consideration in the outer query; if this number is less than 5, then the row under consideration is in the top 5 (for that department).