Window function syntax and usage (Ranking)
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 } ]
)
Copy
Arguments¶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).
The query below shows how to assign row numbers within partitions. In this case, the partitions are stock exchanges (for example, “N” for “NASDAQ”).
SELECT
symbol,
exchange,
shares,
ROW_NUMBER() OVER (PARTITION BY exchange ORDER BY shares) AS row_number
FROM trades;
Copy
+------+--------+------+----------+ |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| +------+--------+------+----------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4