Maximum salary of employee in specific department

Question

EmpID   EmpName EmpSalary   EmpDept
1       Steve    5000       HR
2       Robert   5000       Management
3       Brad     3000       HR
4       Sam      4000       HR
5       Dave     2000       Management
6       Stuvart  4500       Management

How to get employee details from the EMPLOYEE table whose salary is max and he is belong to HR department... My query is

SELECT EmpID,EmpName,EmpSalary,EmpDept 
FROM EMPLOYEE 
WHERE EmpSalary IN (select max(EmpSalary) from EMPLOYEE) 
AND EMPDEPT='HR'

I tried above query, it is giving me the accurate result, but due to performance issue I can not use inner queries.

potashin · Accepted Answer · 2016-03-12T17:10:51.240

2

You can use an order by clause with rownum for Oracle version < 12c:

SELECT EmpID, EmpName, EmpSalary, EmpDept 
FROM EMPLOYEE 
WHERE ROWNUM = 1 AND EMPDEPT = 'HR'
ORDER BY EmpSalary DESC

Otherwise you can use the following:

SELECT EmpID, EmpName, EmpSalary, EmpDept 
FROM EMPLOYEE 
WHERE EMPDEPT = 'HR'
ORDER BY EmpSalary DESC
FETCH FIRST ROW WITH TIES

P.S.: with ties option brings you an opportunity to get multiple results in case there are multiple employees with the same max salary (so resulting set may differ from the one using rownum, for which there would be always one row in the resulting set).

edited Mar 12 '16 at 17:10

answered Mar 07 '16 at 02:46

potashin

44,205
11
83
107

1

Order by is not working, at the time of executing that query first row is with highest salary, but after adding an employee with salary 6000, and dept "HR'. the query still giving the first row – Ranjith Reddy Mar 07 '16 at 04:06
I installed 12 c , and I tried Fetch, it is also giving the same result @potashin – Ranjith Reddy Mar 07 '16 at 05:50
@RanjithReddy: I was mistaken, it is not the `fetch` vs `rownum` issue (I actually don't know what the issue is, just to clarify things – is `EmpSalary` numeric?). And are you still facing the same problem? – potashin Mar 08 '16 at 16:03
yes EmpSalary is numeric and I am still facing that problem @potashin – Ranjith Reddy Mar 08 '16 at 20:58
@RanjithReddy: What result do you get without `fetch <..>` clause? – potashin Mar 08 '16 at 22:01

score 1 · Answer 2 · edited Mar 07 '16 at 02:51

1

SELECT EmpID,EmpName,MAX(EmpSalary),EmpDept
FROM Employee
WHERE EmpDept='HR'
GROUP BY EmpSalary

edited Mar 07 '16 at 02:51

Ranjith Reddy

183
2
12

answered Mar 07 '16 at 01:45

Munawir

3,346
9
33
51

ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 1 Column: 8 – Ranjith Reddy Mar 07 '16 at 02:13

score 0 · Answer 3 · answered Mar 07 '16 at 01:49

0

This should do the trick.

SELECT Max(EmpSalary)
FROM   employees
WHERE  EmpDept = 'HR';

You want to get the max salary from the column EmpSalary because that is where the salaries are being stored. Then you want to distinguish that you are only interested in HR. This information is stored in EmpDept. employees is the name of the table, you did not say what your table was named and that could be different in your case.

answered Mar 07 '16 at 01:49

mikefaheysd

126
4

I need that entire row, so that I can map it to a pojo object then do enrichment. – Ranjith Reddy Mar 07 '16 at 02:13

SkyWalker · Answer 4 · 2016-03-07T02:41:47.600

0

Try it. Hope it will give you quell.

select emp.*
from employee emp
inner join (select EmpDept, max(salary) ms from employee group by EmpDept) m
  on emp.EmpDept = "HR" and emp.salary = m.ms

edited Mar 07 '16 at 02:41

answered Mar 07 '16 at 02:35

SkyWalker

28,384
14
74
132

score 0 · Answer 5 · answered Mar 07 '16 at 16:31

One of the ways, working in Oracle 11g (as you tagged your question), not mentioned in other answers is analytical max() function. It allows query to see values in other rows while not losing details from current row. For HR department it is:

select EmpID, EmpName, EmpSalary, EmpDept 
  from (select EmpID, EmpName, EmpSalary, EmpDept, 
               max(EmpSalary) over () as max_sal
          from employee where empdept = 'HR')
  where EmpSalary = max_sal

 EMPID EMPNAME    EMPSALARY EMPDEPT
------ ---------- --------- ----------
     1 Steve           5000 HR

Similar query showing all employees having maximum salaries in their departments:

select EmpID, EmpName, EmpSalary, EmpDept 
  from (select EmpID, EmpName, EmpSalary, EmpDept, 
               max(EmpSalary) over (partition by EmpDept) as max_sal
          from employee)
  where EmpSalary = max_sal

 EMPID EMPNAME    EMPSALARY EMPDEPT
------ ---------- --------- ----------
     1 Steve           5000 HR
     2 Robert          5000 Management

Maximum salary of employee in specific department

5 Answers5