CODEWITHSUNDEEP
sqlgroup-bymax
Shobhit Mittal
Shobhit Mittal

Reputation: 9

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

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

Upvotes: 0

Views: 266

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

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

Upvotes: 2

forpas
forpas

Reputation: 164089

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

Upvotes: 0

Rex5
Rex5

Reputation: 767

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!).

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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)

Upvotes: 1

Related Questions