I need to fetch the second highest salary per department using correlated subquery and oracle sql

Question

if I run the code like that I get the result I need, but I also need to add the name column, and once I add it, the result changes

select  department_id, max(salary)
from employees e1
where salary < 
(select max(salary) 
from employees e2
where e2.department_id=e1.department_id)
group by department_id
order by department_id;

Answer 1

You can use row_number() window function with common table expression instead of using the subquery:

with cte as
(
  select  department_id, row_number()over(partition by department_id order by salary 
  desc) rn, name 
  from employees e1
)
select  department_id, salary, name 
from cte where rn=2

Just adding name column in select list should do the trick

select  department_id, max(salary),name
from employees e1
where salary < 
(select max(salary) 
from employees e2
where e2.department_id=e1.department_id)
group by department_id
order by department_id;