DENSE_RANK OVER

Reference material for DENSE_RANK function

Rank the current row within the requested window.

For more information on usage, please refer to Window Functions.

Syntax

DENSE_RANK() 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 ranked.	Any

Return Types

Same as input type

Example

In this example, players are ranked by their high score within each game level. Unlike RANK, DENSE_RANK assigns consecutive ranks with no gaps — players with equal scores share the same rank and the next rank is incremented by one.

SELECT
    nickname,
    level,
    highscore,
    DENSE_RANK() OVER (PARTITION BY level ORDER BY highscore DESC) AS game_rank
FROM
    (VALUES
        ('kennethpark', 9, 76),
        ('burchdenise', 9, 89),
        ('sabrina21', 9, 89),
        ('ymatthews', 9, 75)
    ) AS t(nickname, level, highscore)
ORDER BY level, nickname;

nickname text	level int	highscore int	game_rank long
burchdenise	9	89	1
kennethpark	9	76	2
sabrina21	9	89	1
ymatthews	9	75	3

Rows: 4Execution time: 2.03ms