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