Query with 2 variables beats 99% of submissions (737 ms)


  • 5
    Y

    Note: for each department, if there exists duplicate 1st/2nd/3rd highest salaries, all entries should be returned.

    select d.Name as Department, e.Name as Employee, computed.Salary as Salary
    from Employee e, 
    	(
    		select Salary, DepartmentId, @row := IF(DepartmentId=@did, @row + 1,1) as Rank , @did:=DepartmentId
    		from (
    			select distinct Salary, DepartmentId
    			from Employee
    			order by DepartmentId, Salary desc
    			) ordered, (select @row:=0, @did:=0) variables
    	) computed,
    	Department d
    where e.Salary=computed.Salary 
    and e.DepartmentId=computed.DepartmentId 
    and computed.DepartmentId=d.Id
    and computed.Rank<=3
    order by computed.DepartmentId, Salary desc
    

  • 0
    K

    Noob Question: How did you come to know that this query beats 94% submissions?


  • 1
    Y

    @kinankag once you submit the code, you can check out the Accepted Solutions Runtime Distribution from the acceptance page.


  • 0
    H

    My solution is almost same, yet not getting accepted, can you spot the error,

    select d.name as Department, e1.name as Employee, e1.salary as Salary from
    (select e.id, e.name, e.salary, e.departmentid,
    @rank := if(@prevdeptid = e.departmentid,@rank+1,1) as rank,
    @prevdeptid := e.departmentid as prevdeptid
    from employee e, (select @rank = 0,@prevdeptid := -1) r
    order by e.departmentid, e.salary desc) e1,
    department d
    where e1.departmentid = d.id and e1.rank <= 3


Log in to reply
 

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