Categories:

Window Functions (Rank-related)

# ROW_NUMBER¶

Returns a unique row number for each row within a window partition.

The row number starts at 1 and continues up sequentially.

## Syntax¶

ROW_NUMBER() OVER (
[ PARTITION BY <expr1> [, <expr2> ... ] ]
ORDER BY <expr3> [ , <expr4> ... ] [ { ASC | DESC } ]
)


None.

## Usage Notes¶

• expr1 and expr2 specify the column(s) or expression(s) to partition by. You can partition by 0, 1, or more expressions.

For example, suppose that you are selecting data across multiple states (or provinces) and you want row numbers from 1 to N within each state; in that case, you can partition by the state.

If you want only a single group, then omit the PARTITION BY clause.

• expr3 and expr4 specify the column(s) or expression(s) to use to determine the order of the rows. You can order by 1 or more expressions.

For example, if want to list farmers in order by production of corn, then use the bushels_produced column. For details, see Examples (in this topic).

## Examples¶

Show farmers in descending order by amount of corn produced, and assigning row numbers in that order (the farmer who produces the most corn will have row number 1).

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

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


The query below shows how to assign row numbers within partitions. In this case, the partitions are stock exchanges (e.g. “N” for “NASDAQ”).

SELECT
symbol,
exchange,
shares,
ROW_NUMBER() OVER (PARTITION BY exchange ORDER BY shares) AS row_number

+------+--------+------+----------+
|SYMBOL|EXCHANGE|SHARES|ROW_NUMBER|
+------+--------+------+----------+
|SPY   |C       |   250|         1|
|AAPL  |C       |   250|         2|
|AAPL  |C       |   300|         3|
|SPY   |N       |   100|         1|
|AAPL  |N       |   300|         2|
|SPY   |N       |   500|         3|
|QQQ   |N       |   800|         4|
|QQQ   |N       |  2000|         5|
|YHOO  |N       |  5000|         6|
+------+--------+------+----------+