Solution using SUBSTRING_INDEX, scores > 91%


  • 0
    S

    GROUP_CONCAT creates a string of all salaries and SUBSTRING_INDEX picks top 3

    SELECT d.name as Department, e.name as Employee, Salary
    FROM
        Employee e
        INNER JOIN Department d ON (e.DepartmentId = d.Id)
        LEFT JOIN
            (SELECT DepartmentId, 
                    SUBSTRING_INDEX(
                        GROUP_CONCAT(DISTINCT Salary 
                                     ORDER BY Salary 
                                     DESC SEPARATOR ',')
                       , ',', 3) 
                       as TopThreeSalaries
             FROM Employee
             GROUP BY DepartmentId
            ) as dept_sal USING (DepartmentId)
    WHERE
        FIND_IN_SET(Salary, TopThreeSalaries) > 0
    ORDER BY Department, Salary
    

Log in to reply
 

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