Highest Salary in each department

Question

I have a table EmpDetails:

DeptID      EmpName   Salary
Engg        Sam       1000
Engg        Smith     2000
HR          Denis     1500
HR          Danny     3000
IT          David     2000
IT          John      3000

I need to make a query that find the highest salary for each department.

Answer 1

Thank you @JoeStefanelli for his answer (https://stackoverflow.com/a/8477083/4691279). He provided SQL Server 2005+ version and I used the same to create the Oracle version:

WITH cteRowNum(dep_id, emp_id, Salary, RowNums) AS (
    SELECT dep_id, emp_id, Salary,
           DENSE_RANK() OVER(PARTITION BY dep_id ORDER BY Salary DESC) AS RowNums
        FROM employee
)
SELECT cteRowNum.dep_id, cteRowNum.emp_id, cteRowNum.Salary
    FROM cteRowNum
    WHERE cteRowNum.RowNums = 1;

You can test this using livesql.oracle.com, below are my DDLs and DMLs you can use:

create table employee (
emp_id varchar2(50) NOT NULL,
dep_id varchar2(50) NOT NULL,
salary number not null
);

create table department (
dep_id varchar2(50) NOT NULL,
dep_name varchar2(50) NOT NULL
);

insert into employee values (100, 5000, 1000000);
insert into employee values (200, 5000, 2000000);
insert into employee values (300, 5000, 3000000);
insert into employee values (400, 6000, 1500000);
insert into employee values (500, 6000, 1500000);
insert into employee values (600, 7000, 1000000);
insert into employee values (700, 7000, 1000000);
insert into employee values (800, 7000, 2000000);

insert into department values (5000, 'dep 1');
insert into department values (6000, 'dep 2');
insert into department values (7000, 'dep 3');

And below is the success screenshot of the query:

Answer 2

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

The above query is the accepted answer but it will not work for the following scenario. Let's say we have to find the employees with the highest salary in each department for the below table.

DeptID	EmpName	Salary
Engg	Sam	1000
Engg	Smith	2000
Engg	Tom	2000
HR	Denis	1500
HR	Danny	3000
IT	David	2000
IT	John	3000

Notice that Smith and Tom belong to the Engg department and both have the same salary, which is the highest in the Engg department. Hence the query "SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID" will not work since MAX() returns a single value. The below query will work.

SELECT DeptID, EmpName, Salary FROM EmpDetails WHERE (DeptID,Salary) IN (SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID)

Output will be

DeptID	EmpName	Salary
Engg	Smith	2000
Engg	Tom	2000
HR	Danny	3000
IT	John	3000

Answer 3

I have like 2 approaches using one with Rank and the other with ROW_NUMBER

This is my sample data

Age          Name                                               Gender     Salary
----------- -------------------------------------------------- ---------- -----------
1           Mark                                               Male       5000
2           John                                               Male       4500
3           Pavan                                              Male       5000
4           Pam                                                Female     5500
5           Sara                                               Female     4000
6           Aradhya                                            Female     3500
7           Tom                                                Male       5500
8           Mary                                               Female     5000
9           Ben                                                Male       6500
10          Jodi                                               Female     7000
11          Tom                                                Male       5500
12          Ron                                                Male       5000
13          Ramani                                             Female     7000

So here is my first query to find max salary and the person with that max salary for each Gender

    with CTE as(
    select RANK() over(partition by Gender Order by Salary desc) as [Rank],* from employees)
    select * from CTE where [Rank]=1


Rank                 Age          Name                                               Gender     Salary
-------------------- ----------- -------------------------------------------------- ---------- -----------
1                    10          Jodi                                               Female     7000
1                    13          Ramani                                             Female     7000
1                    9           Ben                                                Male       6500

So in this case, we can see there is a tie between these 2 female employees "Jodi" and "Ramani". In that case, As a tie-breaker I want to make use of Age as a deciding factor and person with more age is supposed to be displayed

with CTE as(
select RANK() over(partition by Gender Order by Salary desc,age desc) as [Rank],* from employees)
select * from CTE where [Rank]=1

Rank                 Age          Name                                               Gender     Salary
-------------------- ----------- -------------------------------------------------- ---------- -----------
1                    13          Ramani                                             Female     7000
1                    9           Ben                                                Male       6500

Usually, in this case for finding the highest salary, it doesn't make much difference even if Rank, Dense_Rank, or Row_Number() are used. But they have some impact in other cases.

Answer 4

select T1.* from (select empname as e1,department_name,salary from employee ) as T1, (select max(salary) maxsal,department_name dept1 from employee group by department_name) as T2 where T1.department_name=T2.dept1 and T1.salary=maxsal;

Answer 5

Assuming SQL Server 2005+

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           DENSE_RANK() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary
    FROM cteRowNum
    WHERE RowNum = 1;

Answer 6

with ctesal as (
 select DepartmentId , Name , Salary, ROW_Number() OVER (partition by DepartmentId 
 order by Salary desc) as RowNum
 from dbo.Employee
 )
 select DepartmentId , Name , Salary , RowNum from ctesal where RowNum  =2;

This is applicable to SQL server. ROW_Number is a inbuilt function in SQL server .It gives count starting from 1 based on partition by and order by clause. At the end, We can write where condition based on our requirements.

Answer 7

select deptid, empname, salary from
(Select deptid, empname,salary,
rank() Over(Partition by deptid  order by salary desc)as rank from 
EmpDetails) emp 
where emp.rank = 1

First ranks each employee by salary in descending order having highest rank 1 and then selects only deptid, empname, salary. You can do this for all Nth member of the group.

Answer 8

If you just want to get the highest salary from that table, by department:

SELECT MAX(Salary) FROM TableName GROUP BY DeptID

Answer 9

This is the best possible solution for ORACLE:

Select * from (select customerid, city, freight,
row_number() over (partition by customerid order by freight desc) Row_Number from 
(select orders.orderId, customers.CUSTOMERID, customers.city, orders.FREIGHT from orders inner join customers on orders.customerid = customers.customerid where customers.country='Germany' order by customers.customerid, orders.freight desc) 
order by customerid, freight desc) where Row_Number<=2;

Notice here I have used partition by clause for marking row number, this is majorly because we need to partition the records grouping them according to customer id. I have used two inner queries here. The inner most query is to give a view which is sorted according to customer ID and decreasing order of cost. Now from that we need to obtain always top two records so firstly we need to name them and then we need to filter them according to rownum. Second level query is to mark rownum according to customer ID. And final query will filter the result according to rownum. For every partition.

Answer 10

The below listed query will list highest salary in each department.

select deptname, max(salary) from department, employee where 
  department.deptno=employee.deptno group by deptname;

I executed this query successfully on Oracle database.

Answer 11

The below query will display employee name with their respective department name in which that particular employee name is having highest salary.

with T as
(select empname, employee.deptno, salary
from employee
where salary in (select max(salary)
from employee
group by deptno))
select empname, deptname, salary
from T, department
where T.deptno=department.deptno;

I executed the above query successfully on Oracle database.

Answer 12

Use correlated subquery:

SELECT DeptID, EmpName, Salary
FROM EmpDetails a
WHERE Salary = (SELECT MAX(Salary) 
                FROM EmpDetails b
                WHERE a.DeptID = b.DeptID)

Answer 13

IF you want Department and highest salary, use

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

if you want more columns in employee and department, use

select  Department.Name , emp.Name, emp.Salary from Employee emp
inner join (select DeptID, max(salary) [salary] from employee group by DeptID) b
on emp.DeptID = b.DeptID and b.salary = emp.Salary
inner join Department on emp.DeptID = Department.id
order by Department.Name

if use salary in (select max(salary...)) like this, one person have same salary in another department then it will fail.

Answer 14

select * from (
    select a.* from EmpDetails a 
    right join (select DeptID,max(salary) as Salary from EmpDetails group by DeptID) b
    on b.DeptID=a.DeptID and b.salary=a.salary ) as c  group by c.DeptID;

Answer 15

select empno 
from EMP e 
where salary=(select max(sal) 
              from EMP w 
              where  groupby w.deptno having e.deptno=w.deptno)

I hope it will work...

Answer 16

Use the below quesry:

select employee_name,salary,department_id from emp where salary in(select max(salary) from emp group by department_id);

Answer 17

***

> /*highest salary by each dept*/

***
select d.Dept_Name,max(e.salary)
    from emp_details as e join Dept_Details as d
    on e.d_id=d.Dept_Id
    group by  d.Dept_Name

select  distinct e.d_id,d.Dept_Name
    from emp_details as e join Dept_Details as d 
    on e.d_id=d.Dept_Id

select  e.salary,d.Dept_Name,d.Dept_Id
    from emp_details as e join Dept_Details as d 
    on e.d_id=d.Dept_Id

/////simplest query for max salary dept_wise////

Answer 18

SELECT D.DeptID, E.EmpName, E.Salary
FROM Employee E
INNER JOIN Department D ON D.DeptId = E.DeptId
WHERE E.Salary IN (SELECT MAX(Salary) FROM Employee);

Answer 19

SELECT
    DeptID,
    Salary
FROM
    EmpDetails
GROUP BY
    DeptID
ORDER BY
    Salary desc

Answer 20

SELECT DeptID, MAX(Salary)
 FROM EmpDetails
GROUP BY DeptID

This query will work fine, but the moment if you want to fetch some others details related to the employee having the highest salary will contradict. You can use :

SELECT DepatID, a , b, c
 FROM EmpDetails
 WHERE Salary IN (
    SELECT max(Salary)
      FROM EmpDetails
     GROUP BY DeptID
 );

if you will use the previous query it will only reflects the records of the min val except the salary as you have used the max function.

Answer 21

Not sure why no one has mentioned the Group By .... Having syntax. It specifically addresses these requirements.

select EmpName,DeptId,Salary from EmpDetails group by DeptId having Salary=max(Salary);

Answer 22

Use following command;

SELECT  A.*
    FROM    @EmpDetails A
            INNER JOIN ( SELECT DeptID ,
                                MAX(salary) AS salary
                         FROM   @EmpDetails
                         GROUP BY DeptID
                       ) B ON A.DeptID = B.DeptID
                              AND A.salary = B.salary
    ORDER BY A.DeptID

Answer 23

Select empname,empid,Sal,DeptName from 
(Select e.empname,e.empid,Max(S.Salary) Sal,D.DeptName, ROW_NUMBER() Over(partition by D.DeptName order by s.salary desc) Rownum
from emp e inner join Sal S
on e.empid=s.empid 
inner join Dept d on e.Deptid=d.Deptid
group by e.empname,e.empid,D.DeptName,s.Salary
) x where Rownum = 1

Answer 24

If you want to show other parameters too along with DeptId and Salary like EmpName, EmpId

SELECT 
        EmpID 
      , Name, 
      , Salary
      , DeptId 
   FROM Employee 
   where 
     (DeptId,Salary) 
     in 
     (select DeptId, max(salary) from Employee group by DeptId)

Answer 25

This will work if the department, salary and employee name are in the same table.

select ed.emp_name, ed.salary, ed.dept from
(select max(salary) maxSal, dept from emp_dept group by dept) maxsaldept
inner join emp_dept ed
on ed.dept = maxsaldept.dept and ed.salary = maxsaldept.maxSal

Is there any better solution than this?

Answer 26

Here is a way to get maximum values and names on any version of SQL.

Test Data:

CREATE TABLE EmpDetails(DeptID VARCHAR(10), EmpName VARCHAR(10), Salary DECIMAL(8,2))
INSERT INTO EmpDetails VALUES('Engg','Sam',1000)
INSERT INTO EmpDetails VALUES('Engg','Smith',2000)
INSERT INTO EmpDetails VALUES('HR','Denis',1500)
INSERT INTO EmpDetails VALUES('HR','Danny',3000)
INSERT INTO EmpDetails VALUES('IT','David',2000)
INSERT INTO EmpDetails VALUES('IT','John',3000)

Example:

SELECT ed.DeptID
      ,ed.EmpName
      ,ed.Salary
FROM (SELECT DeptID, MAX(Salary) MaxSal
      FROM EmpDetails
      GROUP BY DeptID)AS empmaxsal
INNER JOIN EmpDetails ed
  ON empmaxsal.DeptID = ed.DeptID
 AND empmaxsal.MaxSal = ed.Salary

Not the most elegant, but it works.

Answer 27

SELECT Employee_ID
     , First_name
     , last_name
     , department_id
     , Salary 
FROM (SELECT Employee_ID
           , First_name
           , last_name
           , department_id
           , Salary
           , MAX(salary) OVER (PARTITION BY department_id) dept_max_sal
      FROM EMPLOYEES) AS Emp
WHERE salary = dept_max_sal;

Answer 28

SELECT empName,empDept,EmpSalary
FROM Employee
WHERE empSalary IN
  (SELECT max(empSalary) AS salary
   From Employee
   GROUP BY EmpDept)

Answer 29

select a.*
 from EmpDetails a
 inner join 
 (
 select DeptID,max(Salary) as Salary
 from EmpDetails group by DeptID  
 )b 
on a.DeptID = b.DeptID and a.Salary = b.Salary

Answer 30

WITH cteRowNum AS (
    SELECT DeptID, EmpName, Salary,
           ROW_NUMBER() OVER(PARTITION BY DeptID ORDER BY Salary DESC) AS RowNum
        FROM EmpDetails
)
SELECT DeptID, EmpName, Salary,Rownum
    FROM cteRowNum
    WHERE RowNum in(1,2);