CODEWITHSUNDEEP
sqlsql-servert-sql
Sharun
Sharun

Reputation: 3082

Group by clause to get name of highest paid employee

I have a table Employee with fields dept, employee ans salary. I want a query to list Department wise highest salaries and name of the employee with that salary.

I know it is simple. I googled but found answers like this, which lists only the department and salary

SELECT dept, SUM (salary)
FROM employee
GROUP BY dept;

Upvotes: 2

Views: 7977

Answers (5)

user4715048
user4715048

Reputation: 1

select * from (select salary,last_name,dense_rank()
over (order by salary desc)
sal_rank from employees) where sal_rank <=3;

That's all... this is the output below:

SALARY LAST_NAME                   SAL_RANK
---------- ------------------------- ----------
 24000 King                               1
 17000 Kochhar                            2
 17000 De Haan                            2
 14000 Russell                            3

Upvotes: -1

Mamerto Fabian
Mamerto Fabian

Reputation: 46

This will do it.

SELECT E1.DEPT, E2.ENAME, E1.HIGHEST_SALARY
FROM
(SELECT DEPT, MAX(SALARY) HIGHEST_SALARY
FROM EMPLOYEE
GROUP BY DEPT) E1
INNER JOIN EMPLOYEE E2 ON E1.HIGHEST_SALARY = E2.SALARY
AND E1.DEPT = E2.DEPT

Upvotes: 3

Rachcha
Rachcha

Reputation: 8806

  SELECT e.*, d.deptname
    FROM employee e
    JOIN department d ON e.deptid = d.deptid
   WHERE EXISTS (SELECT 1
                   FROM employee e_in
                   JOIN department d_in ON e_in.deptid = d_in.deptid
                  WHERE d_in.deptid = d.deptid
               GROUP BY d_in.deptid
                 HAVING MAX(e_in.salary) = e.salary)

Upvotes: 3

aidan
aidan

Reputation: 9576

SELECT e1.*
FROM employee e1
JOIN (SELECT dept, MAX(salary) FROM employee GROUP BY dept) e2 ON
    e1.dept = e2.dept AND e1.salary = e2.salary

Upvotes: 4

John Woo
John Woo

Reputation: 263943

SQL Server 2008 supports Window Functions which help you get what you want.

WITH recordList
AS
(
    SELECT  dept, employeeName, salary,
            DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) rn
    FROM    employee
)
SELECT dept, employeeName, salary
FROM   recordList
WHERE  rn = 1

Upvotes: 3

Related Questions