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. A DENSE_RANK
function returns a sequence of ranking numbers without any gaps.
DENSE_RANK
executes as follows:
- Sorts partition rows as specified by the
ORDER BY
clause. - 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.Null values are considered equal. For detailed information on how null values are sorted, see NULL Sort Order.
- 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.
- If
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
Syntax
DENSE_RANK() OVER ( [ window-partition-clause ] window-order-clause )
Parameters
OVER()
|
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.
Example
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 ...