- 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 } ]
)
Arguments¶
None.
Usage Notes¶
expr1
andexpr2
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
andexpr4
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
FROM trades;
+------+--------+------+----------+
|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|
+------+--------+------+----------+