DENSE_RANK function

The DENSE_RANK function is an OLAP ranking function that calculates a ranking value for each row in an OLAP window. The return value is an ordinal number, which is based on the required ORDER BY expression in the OVER clause.

Syntax


DENSE_RANK function

|--+-DENSE_RANK +--( )------------------------------------------>
   '-DENSERANK -'        

                                         (1)   
>--| OVER clause for Ranking functions |------------------------|

Notes:

See OVER clause for OLAP window expressions

Usage

The rank of a row is defined as 1 plus the number rankings that precede the ranking of the row. If two or more rows have the same value, these rows get the same rank. However, in contrast to the RANK function, if two or more rows tie, there is no gap in the sequence of ranked values. For example, if two rows are ranked 1, the next ranking is still 2.

This function takes no argument, but the empty parentheses must be specified. If the OVER clause specifies the optional window PARTITION clause, the DENSE_RANK rankings are calculated within the subset of rows that each window partition defines.

Example: DENSE_RANK function

The following query ranks sales people by the amount of their sales. Ranks are consecutive even if multiple sales amounts have the same rank.

SELECT emp_num, sales,
  DENSE_RANK() OVER (ORDER BY sales) AS dense_rank,
  FROM sales;

   emp_num      sales   dense_rank

        101      2,000      1
        102      2,400      2
        103      2,400      2
        104      2,500      3
        105      2,500      3
        106      2,650      4