DENSE_RANK [analytic]

Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY clause.

Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY clause. A DENSE_RANK function returns a sequence of ranking numbers without any gaps.

DENSE_RANK executes as follows:

  1. Sorts partition rows as specified by the ORDER BY clause.

  2. Compares the ORDER BY values of the preceding row and current row and ranks the current row as follows:

    • If ORDER BY values are the same, the current row gets the same ranking as the preceding row.

    • If the ORDER BY values are different, DENSE_RANK increments or decrements the current row's ranking by 1, depending whether sort order is ascending or descending.

DENSE_RANK always changes the ranking by 1, so no gaps appear in the ranking sequence. The largest rank value is the number of unique ORDER BY values returned by the query.

Behavior type

Immutable

Syntax

DENSE_RANK() OVER (
    [ window-partition-clause ]
    window-order-clause  )

Parameters

OVER()
See Analytic Functions.

See Analytic Functions

Compared with RANK

RANK leaves gaps in the ranking sequence, while DENSE_RANK does not. The example below compares the behavior of the two functions.

Examples

The following query invokes RANK and DENSE_RANK to rank customers by annual income. The two functions return different rankings, as follows:

  • If annual_salary contains duplicate values, RANK() inserts duplicate rankings and then skips one or more values—for example, from 4 to 6 and 7 to 9.

  • In the parallel column Dense Rank, DENSE_RANK() also inserts duplicate rankings, but leaves no gaps in the rankings sequence:

=>  SELECT employee_region region, employee_key, annual_salary,
     RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) Rank,
     DENSE_RANK() OVER (PARTITION BY employee_region ORDER BY annual_salary) "Dense Rank"
     FROM employee_dimension;
              region              | employee_key | annual_salary | Rank | Dense Rank
----------------------------------+--------------+---------------+------+------------
 West                             |         5248 |          1200 |    1 |          1
 West                             |         6880 |          1204 |    2 |          2
 West                             |         5700 |          1214 |    3 |          3
 West                             |         9857 |          1218 |    4 |          4
 West                             |         6014 |          1218 |    4 |          4
 West                             |         9221 |          1220 |    6 |          5
 West                             |         7646 |          1222 |    7 |          6
 West                             |         6621 |          1222 |    7 |          6
 West                             |         6488 |          1224 |    9 |          7
 West                             |         7659 |          1226 |   10 |          8
 West                             |         7432 |          1226 |   10 |          8
 West                             |         9905 |          1226 |   10 |          8
 West                             |         9021 |          1228 |   13 |          9
 ...
 West                             |           56 |        963104 | 2794 |       2152
 West                             |          100 |        992363 | 2795 |       2153
 East                             |         8353 |          1200 |    1 |          1
 East                             |         9743 |          1202 |    2 |          2
 East                             |         9975 |          1202 |    2 |          2
 East                             |         9205 |          1204 |    4 |          3
 East                             |         8894 |          1206 |    5 |          4
 East                             |         7740 |          1206 |    5 |          4
 East                             |         7324 |          1208 |    7 |          5
 East                             |         6505 |          1208 |    7 |          5
 East                             |         5404 |          1208 |    7 |          5
 East                             |         5010 |          1208 |    7 |          5
 East                             |         9114 |          1212 |   11 |          6
 ...

See also

SQL analytics