게시일: Dec 12, 2011 6:3:22 AM
- 최대, 최소값을 가진 로우를 추출한다.
- 자체로는 함수가 아니며 분석 함수와 같이 사용하는 키워드이다.
[구문 형식]
집계 함수 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
------------------------------------------------------------