CODEWITHSUNDEEP
sqloracle-database
user2155454
user2155454

Reputation: 95

Find first highest salary employee under manager

In the employee table data is stored as below

Empno ename  salary manager

1     prasad 100    4
2     durga  200    4
3     ggggg  200    4
4     jjjjj  300    6
5     kkkkk  600    6
6     dddddd 700    7

I need to find out the first highest salary employee under manager.

my sql code and output:

select * from employee where salary in
         (select max(salary) from employee group by manager);

output:

Empno ename  salary manager

2     durga  200    4
3     ggggg  200    4
5     kkkkk  600    6
6     dddddd 700    7

Expecting output should be:

2     durga  200    4
5     kkkkk  600    6
6     dddddd 700    7

because salary '200' displaying two times for two employees. My requirement is that display first highest salary employee only under manager.

Can anyone suggest sql query for my expecting output. Thanks in advance

Upvotes: 0

Views: 2417

Answers (3)

ℛɑƒæĿᴿᴹᴿ
ℛɑƒæĿᴿᴹᴿ

Reputation: 5376

Try this query:

SELECT ename, manager, MAX(salary) FROM employee GROUP BY manager;

Live example:

Edit: Oracle Database

SELECT manager, MAX(salary) FROM employee GROUP BY manager;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48875

You can use the ROW_NUMBER window function. For example:

select 
  empno, ename, salary, manager
from (
  select *, 
    row_number() over(partition by manager order by salary desc, ename) as rn
  from employee
) x where rn = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271211

Use a correlated subquery, rather than group by:

select e.*
from employee e
where e.salary = (select max(e2.salary)
                  from employee e2
                  where e2.manager = e.manager
                 );

Your issue is that a maximum salary under one manager may be the salary of someone else -- but not that manager's maximum.

Upvotes: 0

Related Questions