I would appreciate if anyone can help point out where this code in wrong:-
select Department, Employee, Salary, Rank from (select Department.Name as Department, Employee.Name as Employee, Salary, DepartmentId, @rank := if (DepartmentId = @prevDeptId, @rank + 1, 1) as rank, (@prevDeptId := DepartmentId) from (select @rank:=0, @prevDeptId:=NULL) vars, Employee JOIN Department on Employee.DepartmentId = Department.Id order by departmentId asc, salary asc ) q where q.rank < 4
I worked on this problem.
Pls try this-->
(CASE WHEN ((DepartmentId=@did) and (Salary=@Salary)) THEN @row :=@row
WHEN (DepartmentId=@did) THEN @row :=(@row +1)
ELSE @row :=1
as Rank ,
select Name, Salary, DepartmentId
order by DepartmentId, Salary desc
) ordered, (select @row:=0, @did:=0 , @Salary:=0) variables
join Department d
The above soln worked for me .
The issue with ur solution is that it does not take into account when in a dept 2 or more people have same salary and they all are in top 3 bracket salaried people.