Wednesday, October 27, 2021
More

    Department Highest Salary

    Must Read

    Programmerhttp://www.improgrammer.net
    We started this site to inspire young minds to motivate and encourage them towards Programming Language. In this site you will get programming tutorials, tech, programming facts, programming fun and programming blogs.

    The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

    +----+-------+--------+--------------+
    | Id | Name  | Salary | DepartmentId |
    +----+-------+--------+--------------+
    | 1  | Joe   | 70000  | 1            |
    | 2  | Henry | 80000  | 2            |
    | 3  | Sam   | 60000  | 2            |
    | 4  | Max   | 90000  | 1            |
    +----+-------+--------+--------------+
    

    The Department table holds all departments of the company.

    +----+----------+
    | Id | Name     |
    +----+----------+
    | 1  | IT       |
    | 2  | Sales    |
    +----+----------+
    

    Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

    +------------+----------+--------+
    | Department | Employee | Salary |
    +------------+----------+--------+
    | IT         | Max      | 90000  |
    | Sales      | Henry    | 80000  |
    +------------+----------+--------+

    Solution :

    Algorithm

    Since the Employee table contains the Salary and DepartmentId information, we can query the highest salary in a department.

    SELECT
        DepartmentId, MAX(Salary)
    FROM
        Employee
    GROUP BY DepartmentId;
    

    Note: There might be multiple employees having the same highest salary, so it is safe not to include the employee name information in this query.

    | DepartmentId | MAX(Salary) |
    |--------------|-------------|
    | 1            | 90000       |
    | 2            | 80000       |
    

    Then, we can join table Employee and Department, and query the (DepartmentId, Salary) are in the temp table using IN statement as below.

    SELECT
        Department.name AS 'Department',
        Employee.name AS 'Employee',
        Salary
    FROM
        Employee
            JOIN
        Department ON Employee.DepartmentId = Department.Id
    WHERE
        (Employee.DepartmentId , Salary) IN
        (   SELECT
                DepartmentId, MAX(Salary)
            FROM
                Employee
            GROUP BY DepartmentId
        )
    ;

    Optimized: using Inner Join

    SELECT D.Name AS Department, E.Name AS Employee, Salary
    FROM Employee AS E 
    INNER JOIN (SELECT DepartmentId, MAX(Salary) AS max_salary FROM Employee
    GROUP BY DepartmentId) AS S ON E.DepartmentId = S.DepartmentId
    INNER JOIN Department D ON D.Id=E.DepartmentId
    WHERE E.Salary = S.max_salary
    

     

    Previous articleSwap Sex Field
    Next articleRising Temperature

    Latest Articles

    More Recipes Like This