SELECT deptno, -MIN(-sal) max_sal 
FROM scott.emp 
GROUP BY deptno 
ORDER BY 1

SELECT deptno, 
       REGEXP_SUBSTR(LISTAGG(sal,',')  
                     WITHIN GROUP(ORDER BY sal DESC),'[^,]+',1,1) max_sal 
FROM scott.emp 
GROUP BY deptno 
ORDER BY 1

SELECT deptno, AVG(sal) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_sal 
FROM scott.emp 
GROUP BY deptno 
ORDER BY 1

WITH x AS ( 
SELECT deptno, sal, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rn 
FROM scott.emp 
) 
SELECT deptno, sal max_sal 
FROM x 
WHERE rn=1 
ORDER BY 1

SELECT deptno, sal max_sal  
FROM scott.emp 
MATCH_RECOGNIZE ( 
PARTITION BY deptno 
ORDER BY sal DESC 
ALL ROWS PER MATCH 
PATTERN (ISNULL) 
DEFINE ISNULL AS PREV(ISNULL.sal) IS NULL 
)

WITH x AS ( 
SELECT deptno, sal 
FROM scott.emp 
ORDER BY 1,2 
), y AS ( 
SELECT x.*, ROWNUM rn 
FROM x 
) 
SELECT deptno, sal 
FROM y 
WHERE CONNECT_BY_ISLEAF=1 
CONNECT BY deptno=PRIOR deptno 
       AND rn=PRIOR rn+1 
START WITH (deptno, rn) IN (SELECT deptno, MIN(rn) 
                            FROM y 
                            GROUP BY deptno)

SELECT deptno, max_sal 
FROM scott.emp 
MODEL 
RETURN UPDATED ROWS 
PARTITION BY (deptno) 
DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rn) 
MEASURES(sal max_sal, 0 dummy) 
RULES( 
  dummy[1]=1 
) 
ORDER BY 1

SELECT deptno, sal max_sal 
FROM scott.emp a 
WHERE sal>=ALL(SELECT sal 
               FROM scott.emp 
               WHERE deptno=a.deptno) 
GROUP BY deptno, sal 
ORDER BY 1

SELECT deptno, sal max_sal 
FROM scott.emp a 
WHERE NOT EXISTS(SELECT 1 
                 FROM scott.emp 
                 WHERE deptno=a.deptno 
                   AND sal>a.sal) 
GROUP BY deptno, sal 
ORDER BY 1

SELECT a.deptno, a.sal max_sal 
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno 
                                      AND b.sal>a.sal  
WHERE b.empno IS NULL 
GROUP BY a.deptno, a.sal 
ORDER BY 1

SELECT deptno, sal max_sal  
FROM scott.emp 
MINUS 
SELECT deptno, sal 
FROM scott.emp a 
WHERE sal<ANY(SELECT sal  
              FROM scott.emp 
              WHERE deptno=a.deptno)

SELECT deptno, sal max_sal  
FROM scott.emp 
MINUS 
SELECT deptno, sal 
FROM scott.emp a 
WHERE EXISTS(SELECT 1  
             FROM scott.emp 
             WHERE deptno=a.deptno 
               AND sal>a.sal)