오라클 분석 함수 MAX() KEEP( DENSE_RANK FIRST )

SELECT A, MAX(C), MAX(B) KEEP(DENSE_RANK FIRST ORDER BY C DESC)
FROM (
    SELECT 1 A, 'ABC' B, 1234 C FROM DUAL
    UNION ALL
    SELECT 1 A, 'AAA' B, 2222 C FROM DUAL
    UNION ALL
    SELECT 2 A, 'CCC' B, 2311 C FROM DUAL
    UNION ALL
    SELECT 2 A, 'ACC' B, 2355 C FROM DUAL
    UNION ALL
    SELECT 3 A, 'DDD' B, 3333 C FROM DUAL
)
GROUP BY A;

 

결과)

A   MAX(C)   MAX(B) KEEP(DENSE_RANK FIRST ORDER BY C DESC)
----------------------------------------------------
1   2222         aaa
2   2355         acc
3   3333         ddd

 

 

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
【형식】
집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
  2  min(salary) keep (dense_rank first order by salary) "Worst",
  3  max(salary) keep (dense_rank last order by salary) "Best"
  4  from employees
  5  order by id;

     Worst       Best
---------- ----------
       220        250

SQL> select * from employees;

        ID    DEPT_NO NAME           SALARY      BONUS
---------- ---------- ---------- ---------- ----------
      1101         10 Cho               250        125
      1102         20 Joe               240        100
      1103         10 kim               250        100
      1104         20 jijoe             220        100

[출처] Oracle 내부함수