Problem solve Get help with specific problems with your technologies, process and projects.

Top 5 salaried employees, not using TOP

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).

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.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

While this solution is pretty clever.  I think you could also avoided using "TOP" by using ROWNUM or row_number(), particularly since it was stated that this was an Oracle database.  Now that I think on it, I am not altogether sure of that.  In doing a quick check it appears ROWNUM might not work so well on an inner join because of when oracle assign the numbers.  Without an Oracle database in from of me I'm not 100% sure.  Maybe you can do another article on that!