-4

I have task to write but after thinking on it for a long time unfortunately I cant solve it. I have to find manager salary and his/her employee with highest salary. Also I must include employee last name and what percent is his her/salary to manager. I'm working on Oracle default HR database.

Thanks in advance

APC
  • 144,005
  • 19
  • 170
  • 281
Dinis Akhmedov
  • 9
  • 3
    Welcome to Stack Overflow. The [help/on-topic] says *Questions asking for homework help **must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it.*** – Ken White Jun 22 '17 at 15:50
  • 1
    You need an aggregation query. Fortunately there are lots of previous questions on StackOverflow which pose problems like this. Read some of the answers in this [search for `[oracle]`+`[top-n]`](https://stackoverflow.com/questions/tagged/oracle+top-n?sort=votes&pageSize=30): one of them is bound to give you the hint you need. – APC Jun 22 '17 at 16:31

1 Answers1

0
create table employees
( emp_id varchar(20),
 salary numeric,
 manager_id varchar(20)
 )    
;

select te.manager_id, tm.salary manager_salary
  , te.emp_id highest_paid_employee, te.salary employee_salary 
from 
(
  select e.*, row_number() over(partition by manager_id order by salary desc) highest_rank 
  from employees e
) te
, (select distinct emp_id manager_id, salary from employees) tm
where highest_rank=1
  and tm.manager_id=te.manager_id
;
access_granted
  • 1,807
  • 20
  • 25