Oracle's grouping function nesting and table joins

--1 Data Environment Preparation

Emp,dept table below for Scott users

--2 requirements: The highest average wage department number, department name, department average salary

Select D.deptno,d.dname,e.sal
From
Select AVG (SAL) Sal,deptno
From EMP E
GROUP BY Deptno
Have avg (sal) = (select Max (SAL) from EMP Group by DEPTNO)) E
Left JOIN Dept D
On E.deptno=d.deptno;

Some people say, red bold section of superfluous, red part can be written in the following form

Select Max (SAL) Sal,deptno
From EMP E
Group BY Deptno;

Note, however, that grouping functions can be nested, but it is not possible to have column names for grouping conditions when the group functions are nested. So this statement will be a direct error .

In other words, once nested grouping functions are used here, Deptno cannot appear on the column.

Well, someone said, I wrote this.

Select Max (sal) Sal
From EMP E
Group BY Deptno;

This is no longer an error, but the DEPTNO as an association condition is gone.

So for a nested grouping function, and use the table to join, you can write a subquery, sub-query inside the nested grouping function.

Oracle's grouping function nesting and table joins