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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top