Q

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


This was last published in July 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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!
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close