Select EMP with max SAL from each DEPT

Question

I´m having a bad time with a SQL query. I´m using oracle default tables:

'EMP' TABLE

http://imageshack.us/photo/my-images/850/sinttuloxps.png/

AND

'DEPT' TABLE

http://imageshack.us/photo/my-images/19/assayp.png/

I wan´t to get the employee with the highest salary in each department.

I´m using SQLPLUS.

Thanks in advance!

ALSO CODE:

    CREATE TABLE dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13),
    CONSTRAINT dept_pkey PRIMARY KEY (deptno)
);

CREATE TABLE emp (
    empno numeric(4,0) NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric(4,0),
    hiredate date,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0),
    CONSTRAINT emp_pkey PRIMARY KEY (empno),
    CONSTRAINT emp_deptno_fkey FOREIGN KEY (deptno) REFERENCES dept(deptno),
    CONSTRAINT emp_mgr_fkey FOREIGN KEY (mgr) REFERENCES emp(empno)
);

INSERT INTO dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');

insert into emp values (7839, 'KING'   ,    'PRESIDENT',   NULL, to_date('17/11/81','dd/mm/yy') ,      5000,       NULL,     10);        
insert into emp values (7566, 'JONES'  ,    'MANAGER'  ,   7839, to_date('02/04/81','dd/mm/yy') ,      2975,       NULL,     20);        
insert into emp values (7902, 'FORD'   ,    'ANALYST'  ,   7566, to_date('03/12/81','dd/mm/yy') ,      3000,       NULL,     20);        
insert into emp values (7369, 'SMITH'  ,    'CLERK'    ,   7902, to_date('17/12/80','dd/mm/yy') ,       800,       NULL,     20);
insert into emp values (7698, 'BLAKE'  ,    'MANAGER'  ,   7839, to_date('01/05/81','dd/mm/yy') ,      2850,       NULL,     30);        
insert into emp values (7499, 'ALLEN'  ,    'SALESMAN' ,   7698, to_date('20/02/81','dd/mm/yy') ,      1600,        300,     30);        
insert into emp values (7521, 'WARD'   ,    'SALESMAN' ,   7698, to_date('22/02/81','dd/mm/yy') ,      1250,        500,     30);        
insert into emp values (7654, 'MARTIN' ,    'SALESMAN' ,   7698, to_date('28/09/81','dd/mm/yy') ,      1250,       1400,     30);        
insert into emp values (7782, 'CLARK'  ,    'MANAGER'  ,   7839, to_date('09/06/81','dd/mm/yy') ,      2450,       NULL,     10);        
insert into emp values (7788, 'SCOTT'  ,    'ANALYST'  ,   7566, to_date('09/12/82','dd/mm/yy') ,      3000,       NULL,     20);        
insert into emp values (7844, 'TURNER' ,    'SALESMAN' ,   7698, to_date('08/09/81','dd/mm/yy') ,      1500,          0,     30);        
insert into emp values (7876, 'ADAMS'  ,    'CLERK'    ,   7788, to_date('12/01/83','dd/mm/yy') ,      1100,       NULL,     20);        
insert into emp values (7900, 'JAMES'  ,    'CLERK'    ,   7698, to_date('03/12/81','dd/mm/yy') ,       950,       NULL,     30);        
insert into emp values (7934, 'MILLER' ,    'CLERK'    ,   7782, to_date('23/01/82','dd/mm/yy') ,      1300,       NULL,     10);

You should try to put more effort on posting you question. Your data and structure should be posted in the body of the question as text, so it's visible and available for future visitors. — Adriano Carneiro, May 07 '13 at 21:10

score 7 · Answer 1 · answered May 07 '13 at 21:28

7

You can also use the analytical RANK() function:

SELECT * FROM (
  SELECT
    Dept.DeptNo,
    Dept.DName,
    Emp.EName,
    Emp.Sal,
    RANK() OVER (PARTITION BY Dept.DeptNo ORDER BY Emp.Sal DESC) AS DeptSalRank
  FROM Emp
  INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo
)
WHERE DeptSalRank = 1

answered May 07 '13 at 21:28

Ed Gibbs

25,924
4
46
69

Thank you so much! Just another one: Get * about employees that have the same job that any employee working in Chicago. THANKS! – alejandromav May 07 '13 at 21:46
I can give you a start: any employee working in Chicago will be in a department that has `loc = 'CHICAGO'`. So the list of employees working in Chicago is `SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE loc = 'CHICAGO'`. See if you can run with that. If not, please post as a new question as Adrian commented above - it's a pretty good question, and posting it separately will help future users who have a similar question. – Ed Gibbs May 07 '13 at 22:17

score 3 · Accepted Answer · edited May 23 '17 at 11:46

3

Classic greatest-n-per-group query. Here is what you want:

select dept.dname, emp.empno, emp.ename, emp.sal
from emp
inner join dept on emp.deptno = dept.deptno
inner join
(
select emp.deptno, max(emp.sal) sal
from emp
group by emp.deptno
) ss on emp.deptno = ss.deptno and emp.sal = ss.sal
order by emp.sal desc

Here is a working fiddle: http://sqlfiddle.com/#!4/7147b/6

Additionally, you might want to checkout a different approach. Look here (SQL Select only rows with Max Value on a Column) to see an interesting answer on the topic.

edited May 23 '17 at 11:46

Community

1
1

answered May 07 '13 at 21:05

Adriano Carneiro

57,693
12
90
123

Thank you so much! Just another one: Get * about employees that have the same job that any employee working in Chicago. THANKS! – alejandromav May 07 '13 at 21:09
@alex030293 That's just another question. Try to put some effort on the body of the question as well. You should post the data there, as text – Adriano Carneiro May 07 '13 at 21:11

Rakesh Anand · Answer 3 · 2013-08-16T06:17:38.120

2

The following query will omit duplicate values

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;

The following query will include the duplicate values

SELECT * FROM EMP WHERE (DEPTNO,SAL) IN 
( SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO)

edited Aug 16 '13 at 06:17

answered Aug 16 '13 at 06:05

Rakesh Anand

423
6
9

score 0 · Answer 4 · edited Jan 18 '17 at 13:06

0

As short as the question: SELECT DeptID, MAX(Salary) FROM Employees GROUP BY DeptID

edited Jan 18 '17 at 13:06

Petter Friberg

21,252
9
60
109

answered Jan 18 '17 at 13:03

Saurabh Kumar Sarkar

19
1

score 0 · Answer 5 · answered Jun 08 '21 at 23:29

0

Tested. Here is the query:

Select outemp.Employee_ID, outemp.First_Name, outemp.Last_Name
From Hr.Employees outemp, 
HR.Departments outdept, 
(Select Max(e.salary) maxsal, D.Department_ID Dept_ID From HR.EMPLOYEES e, HR.Departments d
WHERE e.Department_ID = d.Department_ID
Group By d.Department_ID) DummyTable
where outemp.Department_ID = outdept.Department_ID 
AND outdept.Department_ID = DummyTable.Dept_ID
AND outemp.Salary = DummyTable.Maxsal

answered Jun 08 '21 at 23:29

Swastik Dash

1

Usually, you should also include explanation of your solution, not just code/result. That way, your answer can be useful to anyone with same type of problem, not just the op particular case. – stove Jun 09 '21 at 15:23

score 0 · Answer 6 · edited Dec 28 '21 at 13:24

0

list of top 10 highest paid employee detail

(SELECT *, RANK() OVER (ORDER BY Emp.Sal DESC)AS DeptSalRank 
FROM Emp INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo) LIMIT 10;

edited Dec 28 '21 at 13:24

Suraj Rao

29,388
11
94
103

answered Dec 28 '21 at 13:19

Navjot Mall

1

Select EMP with max SAL from each DEPT

6 Answers6

Linked