Rank for each department based upon Salary

Question

I have the following table format with 100 values which consists 10 different departments,

Dept  name  salary
1     e1    100
1     e2    120
1     e3    140
1     e4    150

I want to return rank for each department by their salary(max to min) in each department.if the salary is same the rank should be also same.

SELECT    Dept,
          name,
          salary,
          @curRank := @curRank + 1 AS Order_emp
FROM      emp p, (SELECT @curRank := 0) r
ORDER BY Dept, salary DESC;

The above query continuously returns the rank.But I need Rank for each dept.

Thanks in advance.

Answer 1

Another approach without using user defined variables and by using correlated sub query

select a.*,
(select count(distinct salary)
from emp 
where Dept = a.Dept
and a.salary <= salary
) rank
from emp a
order by Dept,rank

DEMO

Explanation

In above query all table data will be fetched along with a rank for each row by using correlated sub query, In sub query count of distinct salary records will be returned from same table but where dept is same (for parent query and sub query table) and salary of outer table is less or equal to the salary of table in sub query so considering following data set

Dept  name  salary
1     e1    100
1     e2    120
1     e3    150
1     e4    150

There are 2 employees who share same salary = 150 so back to our condition for last row from parent table

1     e4    150

sub query will calculate a.150 <= salary from emp and dept is same so there are 2 rows (150,150) in table by doing count distinct salary it will return it as 1, Same case applies on second last row.

Let consider again following row for parent table

1     e2    120

sub query will calculate a.120 <= salary from emp and dept is same so there are 3 rows in table (120,150,150) by doing count distinct salary it will return it as a 2

Hope this makes sense