Categories:

Aggregate Functions (General) , Window Functions (Analytic)

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 will have the same rank.

Syntax

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

Arguments

None.

Usage Notes

  • expr1 specifies the column to partition by.

    For example, suppose that within each state or province, you want to rank farmers in order by the amount of corn they produced. In this case, you partition by state.

    If you want only a single group (e.g. you want to rank all farmers regardless of which state they live in), then omit the PARTITION BY clause.

  • expr2 specifies the column that you want to rank by.

    For example, if you’re ranking farmers to see who produced the most corn (within their state), then you would use the bushels_produced column. For details, see Examples (in this topic).

  • Tie values will result in the same rank value, but unlike RANK, they do not result in gaps in the sequence.

Examples

Create a table and data:

-- Create table and load data.
create or replace table corn_production (farmer_ID INTEGER, state varchar, bushels_produced float);
insert into corn_production (farmer_ID, state, bushels_produced) values 
    (1, 'Iowa', 100),
    (2, 'Iowa', 110),
    (3, 'Kansas', 120),
    (4, 'Kansas', 130);

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

SELECT state, bushels_produced, DENSE_RANK() OVER (ORDER BY bushels_produced DESC)
    FROM corn_production;
+--------+------------------+----------------------------------------------------+
| STATE  | BUSHELS_PRODUCED | DENSE_RANK() OVER (ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+----------------------------------------------------|
| Kansas |              130 |                                                  1 |
| Kansas |              120 |                                                  2 |
| Iowa   |              110 |                                                  3 |
| Iowa   |              100 |                                                  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_produced, DENSE_RANK()
      OVER (PARTITION BY state ORDER BY bushels_produced DESC)
      FROM corn_production;
+--------+------------------+----------------------------------------------------------------+
| STATE  | BUSHELS_PRODUCED |                                                   DENSE_RANK() |
|        |                  |       OVER (PARTITION BY STATE ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+----------------------------------------------------------------|
| Iowa   |              110 |                                                              1 |
| Iowa   |              100 |                                                              2 |
| Kansas |              130 |                                                              1 |
| Kansas |              120 |                                                              2 |
+--------+------------------+----------------------------------------------------------------+

The query and output below show how tie values are handled. Note that the ranks are 1, 2, 3, 3, 4. Unlike with the output from the RANK() function, the rank 4 is not skipped because there was a tie for rank 3.

SELECT state, bushels_produced, DENSE_RANK()
      OVER (ORDER BY bushels_produced DESC)
      FROM corn_production;
+--------+------------------+---------------------------------------------+
| STATE  | BUSHELS_PRODUCED |                                DENSE_RANK() |
|        |                  |       OVER (ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+---------------------------------------------|
| Kansas |              130 |                                           1 |
| Kansas |              120 |                                           2 |
| Iowa   |              110 |                                           3 |
| Iowa   |              110 |                                           3 |
| Iowa   |              100 |                                           4 |
+--------+------------------+---------------------------------------------+