오라클 first last

1. 목적

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.
MIN(B) (DENSE_RANK FIRST ORDER BY A [ASC|DESC])
MAX(B) (DENSE_RANK LAST ORDER BY A [ASC|DESC])

2. 문법

aggregate_function
   KEEP
   (DENSE_RANK FIRST|LAST ORDER BY
    expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
    [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
    ]...
   )
   [ OVER query_partition_clause ]

3. 예제

SELECT department_id
     , MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst"
     , MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)  "Best"
  FROM employees
  GROUP BY department_id
;

DEPARTMENT_ID      Worst       Best
------------- ---------- ----------
           10       4400       4400
           20       6000      13000
           30       2500      11000
           40       6500       6500
           50       2100       8200
           60       4200       9000
           70      10000      10000
           80       6100      14000
           90      17000      24000
          100       6900      12008
          110       8300      12008
                    7000       7000

출처 :
1. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm#SQLRF00641
2. http://3030.tistory.com/88