DENSE_RANK

Returns the rank of a value within a group of values, without gaps in the ranks.

The rank value starts at 1 and continues up sequentially.

If two values are the same, they have the same rank.

Syntax

DENSE_RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ ASC | DESC ] [ <window_frame> ] )

For detailed <window_frame> syntax, see Window function syntax and usage.

Arguments

None.

The function itself takes no arguments because it returns the rank (relative position) of the current row within the window, which is ordered by <expr2>. The ordering of the window determines the rank, so there is no need to pass an additional parameter to the DENSE_RANK function.

Usage notes

• expr1: The column or expression to partition the window by.
  For example, to rank farmers within each state by corn produced, partition by state.
  Omit PARTITION BY to rank across all rows as a single group.
• expr2: The column or expression to order (rank) by.
  For example, use bushels_produced to rank by production volume.
• Tie values result in the same rank value, but unlike RANK, they do not result in gaps in the sequence.

Examples

Show farmers’ corn production in descending order, along with the rank of each individual farmer’s production (highest = 1):

SELECT 
    state,
    bushels,
    RANK() OVER (
        ORDER BY bushels DESC
    ),
    DENSE_RANK() OVER (
        ORDER BY bushels DESC
    )
FROM (
    SELECT 1 AS farmer_ID, 'Iowa' AS state, 100.0 AS bushels UNION ALL
    SELECT 2, 'Iowa', 110.0 UNION ALL
    SELECT 3, 'Kansas', 120.0 UNION ALL
    SELECT 4, 'Kansas', 130.0
) corn_production
ORDER BY bushels DESC;

Output:

+--------+---------+-------------------------------------+-------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (ORDER BY BUSHELS DESC) |
|--------+---------+-------------------------------------+-------------------------------------------|
| Kansas |     130 |                                   1 |                                         1 |
| Kansas |     120 |                                   2 |                                         2 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     100 |                                   4 |                                         4 |
+--------+---------+-------------------------------------+-------------------------------------------+

Within each state, show farmers’ corn production in descending order, along with the rank of each individual farmer’s production (highest = 1):

SELECT state, bushels,
        RANK() OVER (PARTITION BY state ORDER BY bushels DESC),
        DENSE_RANK() OVER (PARTITION BY state ORDER BY bushels DESC)
    FROM (
    SELECT 1 AS farmer_ID, 'Iowa' AS state, 100.0 AS bushels UNION ALL
    SELECT 2, 'Iowa', 110.0 UNION ALL
    SELECT 3, 'Kansas', 120.0 UNION ALL
    SELECT 4, 'Kansas', 130.0
    ) corn_production;

Output:

+--------+---------+--------------------------------------------------------+--------------------------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (PARTITION BY STATE ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (PARTITION BY STATE ORDER BY BUSHELS DESC) |
|--------+---------+--------------------------------------------------------+--------------------------------------------------------------|
| Iowa   |     110 |                                                      1 |                                                            1 |
| Iowa   |     100 |                                                      2 |                                                            2 |
| Kansas |     130 |                                                      1 |                                                            1 |
| Kansas |     120 |                                                      2 |                                                            2 |
+--------+---------+--------------------------------------------------------+--------------------------------------------------------------+

Tie handling comparison for RANK vs DENSE_RANK (note DENSE_RANK has no gaps, e.g., 1, 2, 3, 3, 4):

SELECT state, bushels,
        RANK() OVER (ORDER BY bushels DESC),
        DENSE_RANK() OVER (ORDER BY bushels DESC)
    FROM (
    SELECT 1 AS farmer_ID, 'Iowa' AS state, 100.0 AS bushels UNION ALL
    SELECT 2, 'Iowa', 110.0 UNION ALL
    SELECT 3, 'Kansas', 120.0 UNION ALL
    SELECT 4, 'Kansas', 130.0 UNION ALL
    SELECT 5, 'Iowa', 110.0
    ) corn_production;

Output:

+--------+---------+-------------------------------------+-------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (ORDER BY BUSHELS DESC) |
|--------+---------+-------------------------------------+-------------------------------------------|
| Kansas |     130 |                                   1 |                                         1 |
| Kansas |     120 |                                   2 |                                         2 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     100 |                                   5 |                                         4 |
+--------+---------+-------------------------------------+-------------------------------------------+