게시일: Dec 12, 2011 5:9:10 AM
- RANK() : 중복 순위 다음은 해당 개수만큼 건너뛰고 반환
- DENSE_RANK() : 중복 순위 상관없이 순차적으로 반환
- ROW_NUMBER() : 중복과 관계 없이 무조건 순서대로 반환
select id
, salary
-- 분석 함수의 order by 절을 사용하여 내림차순 정렬한 뒤 rank() 함수가 순위를 반환
, RANK() OVER (ORDER BY salary DESC) ranking1
, DENSE_RANK() OVER (ORDER BY salary DESC) ranking2
, ROW_NUMBER() OVER (ORDER BY salary DESC) ranking3
from (
select 100 id, 39000 salary from dual union all
select 101 id, 30000 salary from dual union all
select 102 id, 9000 salary from dual union all
select 103 id, 17000 salary from dual union all -- 중복
select 104 id, 60000 salary from dual union all
select 105 id, 9500 salary from dual union all
select 106 id, 17000 salary from dual union all -- 중복
select 107 id, 37500 salary from dual union all
select 108 id, 25000 salary from dual union all
select 109 id, 5000 salary from dual union all
select 110 id, 17000 salary from dual -- 중복
) ;
---------------------------------------------
ID SALARY RANKING1 RANKING2 RANKING3
---------------------------------------------
104 60000 1 1 1
100 39000 2 2 2
107 37500 3 3 3
101 30000 4 4 4
108 25000 5 5 5
103 17000 6 6 6
110 17000 6 6 7
106 17000 6 6 8
105 9500 9 7 9
102 9000 10 8 10
109 5000 11 9 11
---------------------------------------------
☞ 응용
- 부서별로 월급이 높은 순위로 2명씩 뽑아보세요~
select a.*
from (select dept
, id
, salary
-- 부서별로 그룹을 나누고 order by 절을 사용하여 내림차순 정렬한 뒤 row_number() 함수가 순위를 반환
, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranking
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
)
) a
where a.ranking < 3 ; -- 2순위까지 조회
-----------------------------------
DEPT ID SALARY RANKING
-----------------------------------
10 100 39000 1
-----------------------------------
20 101 30000 1
20 105 9500 2
-----------------------------------
30 103 17000 1
30 106 17000 2
-----------------------------------
40 104 60000 1
40 107 37500 2
-----------------------------------