ROW_NUMBER Function

Description

The rank of a row is one plus the number of distinct ranks that come before the row in question by using the PARTITION BY clause and outputs as INTEGER and it is used as the analytic function only.

For a more detailed description on the analytic function, see Overview.

Syntax

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

Example of Analytic Function

The following example shows output of the serial number according to the number of Olympic gold medals of each country by year. If the number of gold medals is the same, the sorting follows the alphabetic order of the 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