CODEWITHSUNDEEP
mysqlselectgroup-byhaving
Toxa
Toxa

Reputation: 41

SELECT MAX() FROM TABLE using GROUP BY

I have some table:

name    dep_id  salary
Vasia   5       1000
Oleg    5       1300
Vitia   4       1000
Sacha   3       1100
Kolia   5       1600
Lesha   2       1400
Sergey  4       1200
Marina  5       1300
Olga    5       1500
Igor    4       1400
Valia   3       1500
Erema   4       1500

I need to get the name of the employees with the maximum salary in his department

i.e I need

 Lesha   1400
 Valia   1500
 Erema   1500
 Kolia   1600

I tried:

SELECT name, max(salary) FROM employees GROUP BY dep_id

but this displays incorrect values

how can I do this?

Upvotes: 0

Views: 61

Answers (3)

shruti
shruti

Reputation: 707

Try Below Query for the required output :

    select name,sallary from employees t1 where (dep_id,sallary) in
    (select dep_id,max(sallary) from employees group by dep_id);

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

select t1.name,t2.sallary
from Employees t1 join
     (select dep_id,MAX(Sallary) as Sallary
      from Employees
      group by dep_id) t2 on t1.dep_id=t2.dep_id and t1.sallary=t2.sallary
order by t2.sallary

Result:

name    Sallary
---------------
Lesha   1400
Valia   1500
Erema   1500
Kolia   1600

Demo in SQL Fiddle

Upvotes: 1

juergen d
juergen d

Reputation: 204784

select e1.*
from employees e1
join
(
  SELECT dep_id, max(salary) as msal
  FROM employees
  GROUP BY dep_id
) e2 on e1.dep_id = e2.dep_id
    and e1.salary = e2.msal

Upvotes: 3

Related Questions