Rejected Solution Works on My Server


  • 0
    H

    Example of my results. http://i.imgur.com/X8GQjk1.jpg
    LeetCode.com's compiler can only find the Sales Department employees with this query.

    SELECT Department.Name as "Department", Employee.Name as "Employee", Employee.Salary as "Salary"
    
    FROM Department
    
    LEFT JOIN Employee ON (Employee.Salary =
    (
    SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX((SELECT GROUP_CONCAT(DISTINCT Salary ORDER BY SubEmployee.Salary DESC SEPARATOR ',') FROM Employee SubEmployee WHERE SubEmployee.DepartmentId = Department.Id), ',', 1)), ',', 1))
    )
    OR
    Employee.Salary = 
    (
    SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX((SELECT GROUP_CONCAT(DISTINCT Salary ORDER BY SubEmployee.Salary DESC SEPARATOR ',') FROM Employee SubEmployee WHERE SubEmployee.DepartmentId = Department.Id), ',', 2)), ',', 1))
    )
    OR
    Employee.Salary = 
    (
    SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX((SELECT GROUP_CONCAT(DISTINCT Salary ORDER BY SubEmployee.Salary DESC SEPARATOR ',') FROM Employee SubEmployee WHERE SubEmployee.DepartmentId = Department.Id), ',', 3)), ',', 1))
    )
    )
    AND Employee.DepartmentId = Department.Id
    
    WHERE !ISNULL(Employee.Salary)
    
    GROUP BY Department.Id, Employee.Id
    
    ORDER BY Department.Name ASC, Employee.Salary DESC
    
    ;

Log in to reply
 

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