SELECT MAX(ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) ENAME,DEPTNO,MAX(SAL) AS HIGH_SAL FROM SCOTT.EMP GROUP BY DEPTNO /* Max salary from each Dept */
SELECT DEPTNO,MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL,ENAME) AS LEAST_SAL,MIN(ENAME)KEEP(DENSE_RANK FIRST ORDER BY SAL,ENAME) EMP_LEAST_SAL FROM SCOTT.EMP GROUP BY DEPTNO /* Least salary from each dept */
Can we achieve by combine these two above two queries to get desired output as
DEPTNO | HIGH_SAL | ENAME | LOW_SAL | EMP_WITH LEAST_SAL