집계함수란게 원래 여러행을 하나로 집계하는 거죠.
KEEP 에 의한 정렬결과 1등이 두명이 나온뒤에..
앞에 사용한 집계함수 MAX 에 의해서 둘 중 큰값이 나온 것이죠.
만약 MAX 대신 MIN 을 썼다면 작은 값이 나오겟죠.
그렇다면... MAX 나 MIN 대신 다른집계함수를 사용한다면?
SELECT deptno , MAX(sal) max_sal , wm_concat(ename) KEEP(DENSE_RANK LAST ORDER BY sal) max_ename , MIN(sal) min_sal , wm_concat(ename) KEEP(DENSE_RANK FIRST ORDER BY sal) min_ename FROM emp GROUP BY deptno ORDER BY deptno ;
WITH EMP(DEPTNO, ENAME, SAL) AS( SELECT 10, 'KING', 5000 FROM DUAL UNION ALL SELECT 10, 'CLARK', 2450 FROM DUAL UNION ALL SELECT 10, 'MILLER', 1300 FROM DUAL UNION ALL SELECT 20, 'SCOTT', 3000 FROM DUAL UNION ALL SELECT 20, 'FORD', 3000 FROM DUAL UNION ALL SELECT 20, 'JONES', 2975 FROM DUAL UNION ALL SELECT 20, 'ADAMS', 1100 FROM DUAL UNION ALL SELECT 20, 'SMITH', 800 FROM DUAL UNION ALL SELECT 30, 'BLAKE', 2850 FROM DUAL UNION ALL SELECT 30, 'ALLEN', 1600 FROM DUAL UNION ALL SELECT 30, 'TURNER', 1500 FROM DUAL UNION ALL SELECT 30, 'MARTIN', 1250 FROM DUAL UNION ALL SELECT 30, 'WARD', 1250 FROM DUAL UNION ALL SELECT 30, 'JAMES', 950 FROM DUAL ) , TT AS ( SELECT DEPTNO , ENAME , SAL , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FIRST_RANK , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) LAST_RANK FROM EMP ) SELECT T1.DEPTNO , T1.SAL MAX_SAL , T1.ENAME MAX_ENAME , T2.SAL MIN_SAL , T2.ENAME MIN_ENAME FROM ( SELECT DEPTNO, ENAME, SAL FROM TT WHERE FIRST_RANK = 1 ) T1 , ( SELECT DEPTNO, ENAME, SAL FROM TT WHERE LAST_RANK = 1 ) T2 WHERE T1.DEPTNO = T2.DEPTNO ORDER BY DEPTNO ;
wm_concat 는 10g 부터 사용가능하며 공식지원함수는 아닙니다.
안되는 이유는 글쎄요? 스텐다드에서는 안되는 것일까요?
실행결과는 다음과 같습니다. 'SCOTT,FORD'
참고 : Group By 에서 문자열 합치기 - 버전별 정리