Returns a unique row number for each row within the requested window.
For more information on usage, please refer to Window Functions.
Syntax
ROW_NUMBER() OVER ([PARTITION BY <partition_by>] ORDER BY <order_by> [ASC|DESC] )
Parameters
| Parameter | Description | Supported input types |
|---|
<partition_by> | The expression used for the PARTITION BY clause. | Any |
<order_by> | The expression used in the ORDER BY clause. This parameter determines what value will be used for ROW_NUMBER. | Any |
Return Type
INTEGER
Example
In this example, players in each game level are assigned a unique player ID using ROW_NUMBER.
SELECT
nickname,
level,
ROW_NUMBER() OVER (PARTITION BY level ORDER BY nickname ASC) AS player_id
FROM
(VALUES
('kennethpark', 9),
('ymatthews', 9),
('sabrina21', 10),
('burchdenise', 11)
) AS t(nickname, level)
ORDER BY level, nickname;
| nickname text | level int | player_id long |
|---|
| kennethpark | 9 | 1 |
| ymatthews | 9 | 2 |
| sabrina21 | 10 | 1 |
| burchdenise | 11 | 1 |
Rows: 4Execution time: 5.33ms
