Ask the Expert

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

    Requires Free Membership to View

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 first published in July 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: