how to rank using count function in mysql

Question

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.

Your query has syntax errors and shouldn't compile. If you want a row number, why not use `row_number()`? — Stu, Feb 27 '22 at 18:03
this question looks similar to what you want, btw - https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Ja͢ck, Feb 27 '22 at 18:29

Stu · Answer 1 · 2022-02-27T19:04:34.187

0

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

edited Feb 27 '22 at 19:04

answered Feb 27 '22 at 18:24

Stu

30,392
6
14
33

This doesn't work when a person has the same salary. It should work kinda dense_rank() – user17775951 Feb 27 '22 at 18:49
Your sample data doesn't include this edge case - however perhaps you can amend the criteria to be `and e2.id = e.id`, that should yield equal counts I think. – Stu Feb 27 '22 at 19:00
See this slighty amended [Fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1794a04a7f1b3b49f3940a7a900405ea) – Stu Feb 27 '22 at 19:04
Again, if I want 4 max salaries then your code won't work. – user17775951 Feb 27 '22 at 19:10

score 0 · Accepted Answer · answered Feb 28 '22 at 10:32

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

how to rank using count function in mysql

2 Answers2