Three accpeted solutions


  • 54
    K
    SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
    FROM
    	Employee E,
    	(SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId) T,
    	Department D
    WHERE E.DepartmentId = T.DepartmentId 
      AND E.Salary = T.max
      AND E.DepartmentId = D.id
    
    SELECT D.Name,A.Name,A.Salary 
    FROM 
    	Employee A,
    	Department D   
    WHERE A.DepartmentId = D.Id 
      AND NOT EXISTS 
      (SELECT 1 FROM Employee B WHERE B.Salary > A.Salary AND A.DepartmentId = B.DepartmentId) 
    
    SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary 
    from 
    	Employee E,
    	Department D 
    WHERE E.DepartmentId = D.id 
      AND (DepartmentId,Salary) in 
      (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)

  • 0
    G
    This post is deleted!

  • 0
    D

    mine was same as last one as yours.. awesome :)


  • 0
    D
    This post is deleted!

  • 0
    D

    mine is the same as the third one as well:)


  • 0
    I

    the 3rd one result in: Time Limit Exceeded


  • 2
    M

    I like the first one, easy to understand


  • 1
    K

    optimize the first solution, reduce a table join.

    SELECT d1.Name
        , e1.Name
        , e1.Salary
    FROM (
        SELECT d.Id
            , MAX(d.Name) AS Name
            , MAX(e.Salary) AS Salary
        FROM Department d
        JOIN Employee e
        ON e.DepartmentId = d.Id
        GROUP BY d.Id
    ) d1
    JOIN Employee e1
    ON d1.Id = e1.DepartmentId AND e1.Salary = d1.Salary

  • 0
    J

    Simply using 'WHERE' clauses instead of 'JOIN's might cause your query results to vary depending on your database engine and query optimizer (click for stack overflow).


  • 0
    L

    I use Employee.Id to link M to E, why it does not work as using DepartmentId to link them?

    SELECT D.Name AS Department, E.Name AS Employee, E.Salary
    FROM
    (SELECT Id, Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) M
    JOIN Employee E ON E.Id = M.Id
    JOIN Department D ON E.DepartmentId = D.Id
    
    WHERE M.Salary = E.Salary
    

  • 0
    K

    @lichenyu2014

    In your Derived table M, you are grouping the table on DepartmentId, but you Select the Column Id without any Aggregate Function.

    SELECT D.Name AS Department, E.Name AS Employee, E.Salary
    FROM
    (SELECT DepartmentId, Max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) M
    JOIN Employee E ON E.DepartmentId = M.DepartmentId
    JOIN Department D ON E.DepartmentId = D.Id
    WHERE M.Salary = E.Salary;
    ;
    

    This should work, also you may put the final where clause of Salary as a joining condition on between E and M, as it filters out unwanted records as fast as possible.

    Hope this helps, Cheers!!
    ;


  • 0
    A

    SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary
    FROM Employee E JOIN Department D
    WHERE (DepartmentId,Salary) in
    (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)

    I am trying to modify your 3rd solution to the above one. Is there any issue with this?


  • 0
    Y

    select d.Name as Department,rr.Name as Employee,rr.Salary
    from
    (select e.Name,e.Salary,e.DepartmentId from Employee e,(SELECT Salary,DepartmentId from Employee group by DepartmentId having count(*) = 1 or max(Salary)) r where e.DepartmentId = r.DepartmentId and e.Salary = r.Salary) rr,Department d
    where d.id = rr.DepartmentId
    why this wrong


  • 0

    This solution using subquery is not optimal!
    Pls check out my solution without subquery, max function and follow-up questions:)
    https://discuss.leetcode.com/topic/108599/my-best-solution-super-clean-no-subquery-no-max/2


  • 0
    R

    @jyoun44 said in Three accpeted solutions:

    Simply using 'WHERE' clauses instead of 'JOIN's might cause your query results to vary depending on your database engine and query optimizer (click for stack overflow).

    I agree


Log in to reply
 

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