How can I find the first 5 highest salaried employees in each dept in an Oracle Database (not using TOP)?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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).
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.