RANK Function

Description

The rank of the value in the column value group made by the

PARTITION BY clause is calculated and output as INTEGER. It is used as an analytic function only. When there is another identical rank, the next rank is the number adding the number of the same ranks. For example, when there are three rows of Rank 13, the next rank is 16, not 14. On the contrary, the DENSE_RANK function calculates the next rank by adding 1 to the rank.

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

Syntax

RANK() OVER ( [partition_by_clause] [order_by_clause] )

Example of Analytic Function

The following example shows output of the number of Olympic gold medals of each country and the rank of the countries by year. The next rank of the same rank is calculated by adding the number of the same ranks.

SELECT host_year, nation_code, gold,

RANK() OVER (PARTITION BY host_year ORDER BY gold DESC) AS g_rank

FROM participant;

    host_year  nation_code                  gold       g_rank

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

         1988  'URS'                          55            1

         1988  'GDR'                          37            2

         1988  'USA'                          36            3

         1988  'KOR'                          12            4

         1988  'HUN'                          11            5

         1988  'FRG'                          11            5

         1988  'BUL'                          10            7

         1988  'ROU'                           7            8

         1988  'ITA'                           6            9

         1988  'FRA'                           6            9

         1988  'KEN'                           5           11

         1988  'GBR'                           5           11

         1988  'CHN'                           5           11

...

         1988  'CHI'                           0           32

         1988  'ARG'                           0           32

         1988  'JAM'                           0           32

         1988  'SUI'                           0           32

         1988  'SWE'                           0           32

         1992  'EUN'                          45            1

         1992  'USA'                          37            2

         1992  'GER'                          33            3

...

         2000  'RSA'                           0           52

         2000  'NGR'                           0           52

         2000  'JAM'                           0           52

         2000  'BRA'                           0           52

         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  'UKR'                           9            9

         2004  'CUB'                           9            9

         2004  'GBR'                           9            9

         2004  'KOR'                           9            9

...

         2004  'EST'                           0           57

         2004  'SLO'                           0           57

         2004  'SCG'                           0           57

         2004  'FIN'                           0           57

         2004  'POR'                           0           57

         2004  'MEX'                           0           57

         2004  'LAT'                           0           57

         2004  'PRK'                           0           57