SQL> select
2 emp_id,dept_id,salary,
3 last_value(salary)
4 over( partition by dept_id order by salary desc ) as max_salary
5 from emp01;
EMP_ID DEPT_ID SALARY MAX_SALARY
----- ----- ------- ----------
E01 D01 150 150
E06 D02 600 200
E04 D02 400 200
E02 D02 200 200
E05 D03 500 300
E03 D03 300 300 |