I have data like below:
Now I am trying to find the max 3 earners from each department
. For that, I am trying to resolve my inner query where I am trying to use count(*)
for ranking
but somehow it's not working as expected.
select Employee.*, (SELECT COUNT(*) FROM Employee E WHERE id = E.id AND Salary E.Salary) as employees_who_earn_more from Employee
Output
:
--- ----- ------ ------------ ------
| id| name|salary|departmentId|rownum|
--- ----- ------ ------------ ------
| 1| Joe| 60000| 1| 0|
| 2|Ralph| 30000| 1| 0|
| 3| Joel| 50000| 1| 0|
| 4|Tracy| 55000| 1| 0|
--- ----- ------ ------------ ------
Expected
:
--- ----- ------ ------------ ------
| id| name|salary|departmentId|rownum|
--- ----- ------ ------------ ------
| 1| Joe| 60000| 1| 0|
| 2|Ralph| 30000| 1| 3|
| 3| Joel| 50000| 1| 2|
| 4|Tracy| 55000| 1| 1|
--- ----- ------ ------------ ------
NOTE
: I don't want to use any windowing
functions over here and I do want to consider cases where employees can have the same salary.
CodePudding user response:
Try the following correlated subquery using count
select *, (
select Count(*) -1
from Employees e2
where e2.deptId= e.deptId
and (e2.salary > e.salary or (e2.salary = e.salary and e2.id = e.id))
) rownum
from Employees e;
CodePudding user response:
I solved it using below query:
select *, (
select Count(distinct salary) -1
from employees e2
where e2.deptId= e.deptId
and (e2.salary > e.salary or (e2.salary = e.salary and e2.id <=e.id))
) rownum
from employees e;
This query works as a dense_rank()