MAX OVER

Reference material for MAX function

Returns the maximum value within the requested window.

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

Syntax

MAX( <expression> ) OVER ( [ PARTITION BY <partition_by> ] )

Parameters

Parameter	Description	Supported input types
<expression>	A value used for the MAX function	Any
<partition_by>	An expression used for the PARTITION BY clause.	Any

Return Types

Same as input type

Example

The example below shows how each player’s score compares to the highest score in their level. Unlike a regular MAX() aggregation, the window function keeps one row per player while also showing the partition maximum.

SELECT
    nickname,
    level,
    current_score,
    MAX(current_score) OVER (PARTITION BY level) AS highest_score
FROM
    (VALUES
        ('kennethpark', 9, 76),
        ('sabrina21', 9, 90),
        ('burchdenise', 10, 79),
        ('ymatthews', 10, 85),
        ('rileyjon', 10, 80)
    ) AS t(nickname, level, current_score)
ORDER BY level, nickname;

nickname text	level int	current_score int	highest_score int null
kennethpark	9	76	90
sabrina21	9	90	90
burchdenise	10	79	85
rileyjon	10	80	85
ymatthews	10	85	85

Rows: 5Execution time: 1.21ms