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

I don't have your tables so I'll use Scott's EMP. This is its contents:

SQL> select deptno, ename, sal from emp order by deptno, sal desc;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450   --> 2nd highest in deptno 10
        10 MILLER           1300
        20 SCOTT            3000
        20 FORD             3000
        20 JONES            2975   --> 2nd highest in deptno 20
        20 ADAMS            1100
        20 SMITH             800
        30 BLAKE            2850
        30 ALLEN            1600   --> 2nd highest in deptno 30
        30 TURNER           1500
        30 MARTIN           1250
        30 WARD             1250
        30 JAMES             950

14 rows selected.

This is what you don't want:

SQL> with temp as
  2    (select deptno, ename, sal,
  3       dense_rank() over (partition by deptno order by sal desc) rnk
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where rnk = 2
  9  order by deptno, sal desc;

    DEPTNO ENAME             SAL        RNK
---------- ---------- ---------- ----------
        10 CLARK            2450          2
        20 JONES            2975          2
        30 ALLEN            1600          2

SQL>

---

OK, let's correlate some subqueries, then. Return employees whose salary is

• lower than the highest in their department (line #6) (it would rank as the 1st)
• the highest for the rest of salaries in their department (line #3)

So:

SQL> select e.deptno, e.ename, e.sal
  2  from emp e
  3  where e.sal = (select max(b.sal)
  4                 from emp b
  5                 where b.deptno = e.deptno
  6                   and b.sal < (select max(a.sal)
  7                                from emp a
  8                                where a.deptno = b.deptno
  9                                group by a.deptno
 10                               )
 11                 )
 12  order by e.deptno;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        20 JONES            2975
        30 ALLEN            1600

SQL>

Answer 2

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;