none
SELECT, MAX(), Group by

    Question

  •  

    Hi, I have two tables Employee and Department.

    Employee                            Department

    -EmployeeID                   -DepartmentID 

    -EmployeeName             -DepartmentName

    -Salary

    -DepartmentID

     

    I want to SELECT each departments EmployeeName, who has max salary.

     

    SELECT EmployeeName,MAX(Salary) FROM Employee GROUP BY DepartmentID

     

    I know it won't work. I use CURSOR to solve this. But is there any easy way to do this. Using SELECTs

    .

     

    jeudi 28 février 2008 02:11

Réponses

  • Code Snippet

    SELECT e.EmployeeName, e.DepartmentID

    FROM Employee e

    JOIN (SELECT DepartmentID, MAX(Salary) AS MaxSalary

    FROM Employee

    GROUP BY DepartmentID) em ON e.DepartmentId = em.DepartmentID AND e.Salary = em.MaxSalary

     

     

    Note that if two employees have the same (max) salary for a department, it will list both of them.  Effectively, the derived table (the part in the brackets) is creating a listing of each department, with the highest salary in the department.  Then you JOIN (if you don't specify, it's an INNER JOIN) it back to the employee table, so you're only showing employees for each department whose salary is equal to the max salary.

     

    Make sense?

     

     

    jeudi 28 février 2008 02:48
  • Using Window operator & CTE – for SQL Server 2005,

    Code Snippet

    ;with cte

    as

    (

                    select

                                    e.employeename,

                                    e.departmentid,

                                    e.salary,

                                    max(e.salary) over (partition by e.departmentid) as maxsalary

                    from

                                    employee e

    )

    select employeename, departmentid, salary

    from

                    cte

    where

                    salary = maxsalary

     

     

     

     

    jeudi 28 février 2008 03:27

Toutes les réponses

  • Code Snippet

    SELECT e.EmployeeName, e.DepartmentID

    FROM Employee e

    JOIN (SELECT DepartmentID, MAX(Salary) AS MaxSalary

    FROM Employee

    GROUP BY DepartmentID) em ON e.DepartmentId = em.DepartmentID AND e.Salary = em.MaxSalary

     

     

    Note that if two employees have the same (max) salary for a department, it will list both of them.  Effectively, the derived table (the part in the brackets) is creating a listing of each department, with the highest salary in the department.  Then you JOIN (if you don't specify, it's an INNER JOIN) it back to the employee table, so you're only showing employees for each department whose salary is equal to the max salary.

     

    Make sense?

     

     

    jeudi 28 février 2008 02:48
  • Using Window operator & CTE – for SQL Server 2005,

    Code Snippet

    ;with cte

    as

    (

                    select

                                    e.employeename,

                                    e.departmentid,

                                    e.salary,

                                    max(e.salary) over (partition by e.departmentid) as maxsalary

                    from

                                    employee e

    )

    select employeename, departmentid, salary

    from

                    cte

    where

                    salary = maxsalary

     

     

     

     

    jeudi 28 février 2008 03:27
  • Yes, thanks you. It is amazing. Now I don't need CURSOR. thank you.

    • Proposé comme réponse sandip_sagar samedi 17 décembre 2011 06:37
    lundi 3 mars 2008 11:24