RANK

Returns the rank of a value within an ordered group of values.

• The rank value starts at 1 and continues sequentially.
• If two values are the same, they have the same rank (ties produce gaps).

Syntax

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

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

Arguments

The function takes no arguments. It returns the rank (relative position) of the current row within the window, which is ordered by <expr2>. Because the ordering of the window determines the rank, no parameter is passed to RANK().

Usage notes

• PARTITION BY <expr1> creates independent ranking groups. Omit it to rank across all rows.
• ORDER BY <expr2> determines the ranking sort key(s) and direction.
• Ties receive the same rank; this produces gaps. For example, if two rows tie for rank 3, the next rank is 5.
• To avoid gaps in ranks, use DENSE_RANK instead.
• The ORDER BY inside OVER affects ordering within the window only. Use a separate outer ORDER BY to sort final query output.

Examples

Show farmers’ corn production in descending order, with both RANK and DENSE_RANK (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 |
+--------+---------+--------------------------------------------------------+--------------------------------------------------------------+

The query and output below show how tie values are handled by the RANK and DENSE_RANK functions. Note that for DENSE_RANK, 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,
        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 |
+--------+---------+-------------------------------------+-------------------------------------------+