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;
CodePudding user response:
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;
CodePudding user response:
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>