- Arrange the records according to department id ascending and salary
descending along with a variable indicating the position of the
salary in the respective department.
- Now from the outer select query extract those records whose position is less than or equal to three
SELECT Department. NAME AS Department, t. NAME AS Employee, t.Salary AS Salary FROM Department INNER JOIN ( SELECT Employee. NAME, Employee.Salary, Employee.DepartmentId, CASE WHEN (@prev <> DepartmentId) THEN @count := 1 ELSE ( CASE WHEN (@prevSalary = Salary) THEN @count ELSE @Count := @count + 1 END ) END AS counter, (@prev := DepartmentId) AS dept, (@prevSalary := Salary) AS sal FROM ( SELECT @prev := - 1, @count := 0 ,@prevSalary := - 1 ) c, Employee ORDER BY DepartmentId ASC, Salary DESC ) AS t ON t.DepartmentId = Department.Id WHERE t.counter <= 3