[ORACLE] FIRST, LAST (최대, 최소값을 가진 로우 추출)

FIRST, LAST (최대, 최소값을 가진 로우 추출)

- 최대, 최소값을 가진 로우를 추출한다.

- 자체로는 함수가 아니며 분석 함수와 같이 사용하는 키워드이다.

 

 

[구문 형식]

집계 함수 KEEP ( 함수 FIRST[LAST] ORDER BY expr [ASC | DESC] ) OVER ( PARTITION BY expr .... )

 

☞ 부서별 최대급여, 최소급여

 select dept

      , max(salary)

      , min(salary)

   from (

         select 10 dept, 100 id, 39000 salary from dual union all

         select 20 dept, 101 id, 30000 salary from dual union all

         select 20 dept, 102 id,  9000 salary from dual union all

         select 30 dept, 103 id, 17000 salary from dual union all

         select 40 dept, 104 id, 60000 salary from dual union all

         select 20 dept, 105 id,  9500 salary from dual union all

         select 30 dept, 106 id, 17000 salary from dual union all

         select 40 dept, 107 id, 37500 salary from dual union all

         select 40 dept, 108 id, 25000 salary from dual union all

         select 30 dept, 109 id,  5000 salary from dual union all

         select 40 dept, 110 id, 17000 salary from dual

        )

  group by dept;

 -----------------------------------

 DEPT    MAX(SALARY)     MIN(SALARY)

 -----------------------------------

 10      39000           39000

 20      30000            9000

 30      17000            5000

 40      60000           17000

 -----------------------------------

☞ 부서별 최대급여와 최소급여를 받는 사원을 동시에 추출해보자.

  select dept                                                    "부서"

       , MAX(id) KEEP ( DENSE_RANK FIRST ORDER BY salary DESC )  "최대급여 사원"

       , MAX(salary)                                             "최대급여"

       , MIN(id) KEEP ( DENSE_RANK LAST  ORDER BY salary DESC )  "최소급여 사원"

       , MIN(salary)                                             "최소급여"

    from (

          select 10 dept, 100 id, 39000 salary from dual union all

          select 20 dept, 101 id, 30000 salary from dual union all

          select 20 dept, 102 id,  9000 salary from dual union all

          select 30 dept, 103 id, 17000 salary from dual union all

          select 40 dept, 104 id, 60000 salary from dual union all

          select 20 dept, 105 id,  9500 salary from dual union all

          select 30 dept, 106 id, 17000 salary from dual union all

          select 40 dept, 107 id, 37500 salary from dual union all

          select 40 dept, 108 id, 25000 salary from dual union all

          select 30 dept, 109 id,  5000 salary from dual union all

          select 40 dept, 110 id, 17000 salary from dual

         )

   group by dept;

 ------------------------------------------------------------ 

 부서    최대급여 사원   최대급여    최소급여 사원   최소급여

 ------------------------------------------------------------

 10      100             39000       100             39000

 20      101             30000       102              9000

 30      106             17000       109              5000

 40      104             60000       110             17000

 ------------------------------------------------------------