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;
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
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>