ROW_NUMBER

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

The row number starts at 1 and increases sequentially.

Syntax

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

Arguments

None.

Usage notes

• expr1, expr2: Column(s) or expression(s) to partition by. You can partition by 0, 1, or more expressions.
  • Example: To number rows 1..N within each state, include the state in PARTITION BY.
  • Omit PARTITION BY for a single group over all rows.
• expr3, expr4: Column(s) or expression(s) used to determine the ordering of rows. You can order by 1 or more expressions.
  • Example: To list farmers by corn production, order by bushels_produced.

Examples

The query below shows how to assign row numbers within partitions. In this case, the partitions are stock exchanges (for example, “N” for “NASDAQ”). Assign row numbers within partitions (exchanges), ordered by shares:

SELECT
    symbol,
    exchange,
    shares,
    ROW_NUMBER() OVER (PARTITION BY exchange ORDER BY shares) AS row_number
FROM (
    SELECT 'SPY' AS symbol, 'C' AS exchange, 250 AS shares UNION ALL
    SELECT 'AAPL', 'C', 250 UNION ALL
    SELECT 'AAPL', 'C', 300 UNION ALL
    SELECT 'SPY', 'N', 100 UNION ALL
    SELECT 'AAPL', 'N', 300 UNION ALL
    SELECT 'SPY', 'N', 500 UNION ALL
    SELECT 'QQQ', 'N', 800 UNION ALL
    SELECT 'QQQ', 'N', 2000 UNION ALL
    SELECT 'YHOO', 'N', 5000
) trades
ORDER BY exchange, shares;

Output:

+------+--------+------+----------+
|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|
+------+--------+------+----------+