CODEWITHSUNDEEP
sqlmaxinner-join
ERJAN
ERJAN

Reputation: 24500

How to get max salary in each dept from 2 tables - sql?

I have 2 tables , department and employee. I need to get employee's name with max salary in each dept.

create table Dep(name_dep char, id_dep int);

insert into Dep values("econ", 1);
insert into Dep values("credit", 2);
insert into Dep values("energy", 3);
insert into Dep values("retail", 4);
insert into Dep values("manufactury", 5);

create table Emp(id_emp int, id_dep int, age int, person_name char, salary int );
insert into Emp values(1, 1, 23, 'john', 200);
insert into Emp values(3, 2, 3, 'dalbai', 100);
insert into Emp values(6, 3, 53, 'borat', 300);
insert into Emp values(7, 1, 63, 'erjan', 1600);
insert into Emp values(9, 2, 73, 'sergey', 1000);
insert into Emp values(8, 5, 83, 'lucy', 20);
insert into Emp values(90, 4, 93, 'mike', 1200);

How to query employee name with max salary in each dept? My query:

SELECT person_name, name_dep
FROM Emp e
INNER JOIN
(
    SELECT name_dep, MAX(salary) AS max_salary
    FROM Dep d
    GROUP BY id_dep
) d
    ON e.id_dep = d.id_dep

Upvotes: 0

Views: 12494

Answers (4)

alexander garcia
alexander garcia

Reputation: 33

I have a table wich works pretty much the same way as your table, in that table I use a correlated subquery

select * from emp inner join dept on emp.deptno = dept.deptno where 
(emp.deptno, sal) in 
(select deptno, max(sal) from emp where deptno = emp.deptno 
group by emp.deptno);

the correlated query fetch the higher salary and department grouping by department, then, the outer query uses the results as conditions to extract information from the tables, I hove It helps

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Though I have corrected the Gordon's answer, But if you strictly need an ANCI approach, You can use below query - 

SELECT e.person_name, d.name_dep, d.max_salary
FROM Emp e
INNER JOIN Dep d ON e.id_dep = d.id_dep
INNER JOIN (SELECT id_dep, MAX(salary) AS max_salary
            FROM Emp
            GROUP BY id_dep) d ON e.id_dep = d.id_dep
                               AND e.salary = d.max_salary

Basically you was using name_dep instead of id_dep which i have corrected.

Upvotes: 2

forpas
forpas

Reputation: 164089

Join the tables and the query that returns the max salary for each department:

select
  d.name_dep, e.person_name, t.salary
from Dep d inner join (
  select id_dep, max(salary) salary
  from Emp
  group by id_dep
) t on t.id_dep = d.id_dep
inner join Emp e on e.id_dep = t.id_dep and e.salary = t.salary

See the demo.
Results:

| name_dep    | person_name | salary |
| ----------- | ----------- | ------ |
| energy      | borat       | 300    |
| econ        | erjan       | 1600   |
| credit      | sergey      | 1000   |
| manufactury | lucy        | 20     |
| retail      | mike        | 1200   |

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Use window functions. For one person, use row_number():

SELECT person_name, name_dep
FROM (SELECT e.person_name, d.name_dep,
             ROW_NUMBER() OVER (PARTITION BY e.id_dep ORDER BY e.salary DESC) as seqnum
      FROM Emp e INNER JOIN
           Dep d
           ON e.id_dep = d.id_dep
     ) ed
WHERE seqnum = 1;

If you want all duplicates in the case of ties, then use RANK().

Upvotes: -1

Related Questions