how to find the maximum salary of employees in a specific department

Question

I have a table named SalaryTable containing salaries of employee in various departments:

dept_id    name   salary
12          a      100
13          b      200
12          c      300
14          d      400
12          e      500
13          f      600

I need to find the maximum salary of each department with given department id AND the name of that person along with maximum salary.

I am using the following sql query for this

select dept_id, name, max(salary)
from SalaryTable
group by salary

But the above code is giving me error: dept_id must be an aggregate expression or appear in GROUP BY clause

I am able to get the following table easily with this below query:

select dept_id, max(salary) as max_salary
from SalaryTable
group by salary 

dept_id          max_salary
12                  500
13                  600
14                  400

but I also need the name of that person as:
REQUIRED OUTPUT

dept_id     name     max_salary
12           e       500
13           f       600
14           d       400

Gordon Linoff · Answer 1 · 2019-06-19T12:08:53.013

2

You appear to be learning SQL, so you can build on what you have. The following gets the maximum salary:

select dept_id, max(salary)
from SalaryTable
group by dept_id;

You can use this as a subquery, to get all matching names:

select st.*
from SalaryTable st join
     (select dept_id, max(salary) as max_salary
      from SalaryTable
      group by dept_id
     ) std
     on st.dept_id = std.dept_id and
        st.salary = std.max_salary

edited Jun 19 '19 at 12:08

answered Jun 19 '19 at 11:08

Gordon Linoff

1,242,037
58
646
786

the requirement is to get all the employees and as the problem statement suggests to get only employees with maximum salary. – kiran gadhe Jun 19 '19 at 11:12

score 1 · Answer 2 · answered Jun 19 '19 at 11:08

1

use correlated subquery

select dept_id, name, salary
from SalaryTable a 
   where salary =(select max(salary) from SalaryTable b where a.dept_id=b.dept_id)

answered Jun 19 '19 at 11:08

Fahmi

37,315
5
22
31

score 0 · Answer 3 · answered Jun 19 '19 at 11:35

To be exact:

SELECT dept_id, NAME, salary FROM SalaryTable a 
WHERE salary =(SELECT MAX(salary) FROM SalaryTable b WHERE a.dept_id=b.dept_id) 
ORDER BY dept_id;

Also see try by joins because see this

Remember: Whatever you put in between select and from in single sql statement that must be used in the group by clause (That's what your error says!).

score 0 · Answer 4 · answered Jun 19 '19 at 11:57

You can do it with NOT EXISTS:

select s.* from SalaryTable s
where not exists (
  select 1 from SalaryTable 
  where dept_id = s.dept_id and salary > s.salary
)
order by s.dept_id

See the demo.
Results:

> dept_id | name | salary
> ------: | :--- | -----:
>      12 | e    |    500
>      13 | f    |    600
>      14 | d    |    400

how to find the maximum salary of employees in a specific department

4 Answers4