DENSE_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. Even when there is the same rank, 1 is added to the next rank value. For example, when there are three rows of Rank 13, the next rank is 14, not 16. On the contrary, the RANK function calculates the next rank by adding the number of same ranks.

For more detailed descriptions on the analytic function, see Overview.

Syntax

DENSE_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 number of the same rank is ignored and the next rank is calculated by adding 1 to the rank.

SELECT host_year, nation_code, gold,

DENSE_RANK() OVER (PARTITION BY host_year ORDER BY gold DESC) AS d_rank

FROM participant;

host_year  nation_code                  gold       d_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            6

         1988  'ROU'                           7            7

         1988  'ITA'                           6            8

         1988  'FRA'                           6            8

         1988  'KEN'                           5            9

         1988  'GBR'                           5            9

         1988  'CHN'                           5            9

...

         1988  'CHI'                           0           14

         1988  'ARG'                           0           14

         1988  'JAM'                           0           14

         1988  'SUI'                           0           14

         1988  'SWE'                           0           14

         1992  'EUN'                          45            1

         1992  'USA'                          37            2

         1992  'GER'                          33            3

...

         2000  'RSA'                           0           15

         2000  'NGR'                           0           15

         2000  'JAM'                           0           15

         2000  'BRA'                           0           15

         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           17

         2004  'SLO'                           0           17

         2004  'SCG'                           0           17

         2004  'FIN'                           0           17

         2004  'POR'                           0           17

         2004  'MEX'                           0           17

         2004  'LAT'                           0           17

         2004  'PRK'                           0           17