How to retrieve max salary of employee from each department

Question

Consider the table Employee:

Department number      Employee_id      Salary
   1                       123           2000
   1                       234           3266
   1                       657           3265
   2                       546           2050
   2                       657           3000
   2                       121           6000
   3                       131           6500
   3                       141           5000
   3                       151           1050

Now I want to retrieve the employee_id having highest salary from each department. How to write the query for this?

Well. .. This was asked in my interview. I don't know the logic to try. — user3605574, May 05 '14 at 21:14
Before you go to that schools site, please see http://w3fools.com. — Greg Hewgill, May 05 '14 at 21:31
This is a variation on the classic [tag:greatest-n-per-group] problem; see answers in this question: [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category). Stuff like this gets too many duplicate questions. — Clockwork-Muse, May 06 '14 at 00:21

score 1 · Answer 1 · answered May 05 '14 at 22:59

A good way to approach these queries is using row_number():

select department_number, employee_id, salary
from (select e.*, row_number() over (partition by department order by salary desc) as seqnum
      from employee e
     ) e
where seqnum = 1;

If you want to get multiple rows for a department when there are ties, then use dense_rank() or rank() instead of row_number().

score 0 · Answer 2 · answered May 05 '14 at 21:50

0

Try this

SELECT Dept_Num,EmpID,Salary
from Employee
WHERE Salary IN (Select MAX(Salary) from Employee Group By Dept_Num)

answered May 05 '14 at 21:50

Karthik Ganesan

4,142
2
26
42

1

@Karthik . . . This does not work in all cases. Consider if the data had the value "1, xxx, 151". – Gordon Linoff May 05 '14 at 23:00

Hitsugaya · Answer 3 · 2014-05-06T19:54:04.897

-3

Can you use the max statement:

Select departament_number,employee_id,salary
from employee
where salary in (select MAX(salary) from employee group by departament_number)

edited May 06 '14 at 19:54

answered May 05 '14 at 21:14

Hitsugaya

79
1
6

Including the employee_id in the group by would result in a result for each employee rather than a single record for the department. – Jeremy May 05 '14 at 21:24

How to retrieve max salary of employee from each department

3 Answers3