Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

ROW_NUMBER 함수

설명

PARTITION BY 절에 의한 칼럼 값의 그룹에서 각 행에 고유한 일련번호를 1부터 순서대로 부여하여 INTEGER로 출력하며, 분석 함수로만 사용된다.

분석 함수에 대한 설명은 개요를 참고한다.

구문

ROW_NUMBER() OVER ( [partition_by_clause] [order_by_clause] )

분석 함수 예제

다음은 역대 올림픽에서 연도별로 금메달을 많이 획득한 국가의 금메달 개수에 따라 일련번호를 출력하되, 금메달 개수가 같은 경우에는 nation_code의 알파벳 순서대로 출력하는 예제이다.

SELECT host_year, nation_code, gold,

ROW_NUMBER() OVER (PARTITION BY host_year ORDER BY gold DESC) AS r_num

FROM participant;

 

    host_year  nation_code                  gold       r_num

=============================================================

         1988  'URS'                          55            1

         1988  'GDR'                          37            2

         1988  'USA'                          36            3

         1988  'KOR'                          12            4

         1988  'FRG'                          11            5

         1988  'HUN'                          11            6

         1988  'BUL'                          10            7

         1988  'ROU'                           7            8

         1988  'FRA'                           6            9

         1988  'ITA'                           6           10

         1988  'CHN'                           5           11

...

         1988  'YEM'                           0          152

         1988  'YMD'                           0          153

         1988  'ZAI'                           0          154

         1988  'ZAM'                           0          155

         1988  'ZIM'                           0          156

         1992  'EUN'                          45            1

         1992  'USA'                          37            2

         1992  'GER'                          33            3

...

         2000  'VIN'                           0          194

         2000  'YEM'                           0          195

         2000  'ZAM'                           0          196

         2000  'ZIM'                           0          197

         2004  'USA'                          36            1

         2004  'CHN'                          32            2

         2004  'RUS'                          27            3

         2004  'AUS'                          17            4

         2004  'JPN'                          16            5

         2004  'GER'                          13            6

         2004  'FRA'                          11            7

         2004  'ITA'                          10            8

         2004  'CUB'                           9            9

         2004  'GBR'                           9           10

         2004  'KOR'                           9           11

...

         2004  'UGA'                           0          195

         2004  'URU'                           0          196

         2004  'VAN'                           0          197

         2004  'VEN'                           0          198

         2004  'VIE'                           0          199

         2004  'VIN'                           0          200

         2004  'YEM'                           0          201

         2004  'ZAM'                           0          202