Accepted Solution with 934 ms using 3 variables


  • 1
    0

    Explanation :

    1. 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.
    1. 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

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.