ROW_NUMBER OVER

Reference material for ROW_NUMBER function

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 below, players in each game level are assigned a player ID.

SELECT
	nickname,
	level,
	ROW_NUMBER() OVER (PARTITION BY level ORDER BY level ASC ) AS player_id
FROM
	players;

Returns:

nickname	level	player_id
ymatthews	9	1
rileyjon	12	2
sabrina21	11	3
burchdenise	10	4