Categories:

Window Functions (Rank-related, Window frame)

# RANK¶

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

The rank value starts at 1 and continues up.

## Syntax¶

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


For details about window_frame syntax, see Window Frame 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 RANK function.

## 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 in the U.S. 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 result in the same rank value; however, gaps in the sequence will result from the number of tie values.

For example, if three rows return 1, RANK will skip 2 and 3 and assign 4 to the next row in the group.

• To avoid gaps, use the DENSE_RANK function instead.

## Examples¶

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

SELECT state, bushels_produced, RANK()
OVER (ORDER BY bushels_produced DESC)
FROM corn_production;

+--------+------------------+------+
|  state | bushels_produced | RANK |
+--------+------------------+------+
| Kansas |              130 |     1|
| Kansas |              120 |     2|
| Iowa   |              110 |     3|
| Iowa   |              100 |     4|
+--------+------------------+------+


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

SELECT state, bushels_produced, RANK()
OVER (PARTITION BY state ORDER BY bushels_produced)
FROM corn_production;

+--------+------------------+------+
|  state | bushels_produced | RANK |
+--------+------------------+------+
| Iowa   |              110 |     1|
| Iowa   |              100 |     2|
| Kansas |              130 |     1|
| Kansas |              120 |     2|
+--------+------------------+------+


In the query and output below, the values for stock symbol SPY show how tie values are handled (note that the ranks are 1, 2, 2, 4; the rank 3 is skipped because there was a tie for rank 2):

SELECT
symbol,
exchange,
price,
RANK() OVER (PARTITION BY symbol ORDER BY price) AS rank

+------+--------+------+----+
|symbol|exchange|price |RANK|
+------+--------+------+----+
|QQQ   |Q       | 83.99|   1|
|QQQ   |N       | 84.35|   2|
|QQQ   |Q       | 87.49|   3|
|QQQ   |N       | 88.50|   4|
|SPY   |P       |185.27|   1|
|SPY   |N       |186.29|   2|
|SPY   |N       |186.29|   2|
|SPY   |C       |188.47|   4|
|MSFT  |Q       | 38.77|   1|
|MSFT  |Q       | 39.55|   2|
|MSFT  |P       | 39.66|   3|
|MSFT  |P       | 40.00|   4|
|YHOO  |N       | 32.89|   1|
|YHOO  |Q       | 33.49|   2|
|YHOO  |Q       | 34.21|   3|
|YHOO  |P       | 34.48|   4|
|AAPL  |P       |568.33|   1|
|AAPL  |C       |569.42|   2|
|AAPL  |C       |570.37|   3|
|AAPL  |N       |571.94|   4|
+------+--------+------+----+