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;

Demo Fiddle

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