Select * from dept
Where deptno=
(select e.d from (
Select deptno d, avg (NVL (sal, 0)) a, row_number () over (order by avg (NVL (sal, 0)) desc) r the from emp group by deptno) e
Where "e.r=1);
Use the table of emp, dept, such can be written as a result, feeling a bit low, studying for oracle, see big shrimp is there a more concise way,

CodePudding user response:

The lower is not lower, but if multiple departments and highest you don't know the SQL whether conform to the requirements

CodePudding user response:

Try this, mobile phone typing, no validation:

 
Select a. * 
The from dept. A, 
(select deptno, row_number () over (order by avg (NVL (sal, 0)) desc) rn from emp) b 
Where a. d. eptno=b.d eptno 
And b.r n=1 

CodePudding user response:

Can also be like this:

 select * from dept 
Where deptno=
(select e.d from (
Select deptno d from emp group by deptno order by avg (NVL (sal, 0)) desc) e 
Where rownum=1); 

But feel about, one is to use row_number (), is a useless row_number (),