오라클 row 순서 관리 RANK() , ROW_NUMBER()

RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해,

ROW_NUMBER 함수는 유니크한 순위를 부여한다.

 

SELECT NAME ,ID

RANK() OVER (ORDER BY NAME) RANK,

ROW_NUMBER() OVER (ORDER BY NAME,ID)  AS ROW_NUMBER

FROM S_DEPT

 

NAME                               ID                             RANK                    ROW_NUMBER

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

ADMIN                              50                                  1                           1

FINANCE                           10                                  2                           2

OPER                                41                                  3                           3

OPER                                42                                  3                           4

OPER                                43                                  3                           5

OPER                                44                                  3                           6

OPER                                45                                  3                           7

SALES                               31                                 8                            8

SALES                               32                                 8                            9

SALES                               33                                 8                            10

SALES                               34                                 8                            11

SALES                               35                                 8                            12 <=유니크한랭크

 

12 ROWS SELECTED

 

두 번째 칼럼인 RANK 열은 'ORDER BY NAME'로만 구분되었기 때문에 NAME이 틀리더라도 동일한 NAME으로 구분하여 동일한  순위를 부여했다.

 

그러나 세번째 칼럼인 ROW_NUMBER 열은 동일한 순위를 배제하기 위해 유니크한 순위를 정하고 잇다. 

 

세부적인 순서까지 관리하고 싶으면

ROW_NUMBER() OVER (ORDER BY NAME,ID) 같이 충분한 정렬 기준을 주어야한다.