Link Search Menu Expand Document

MAX (window function)

Returns the maximum value within the requested window.

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

Syntax

MAX( <exp> ) OVER ( [ PARTITION BY <exp> ] )
Parameter Description
<val> An expression used for the MAX function.
<exp> An expression used for the PARTITION BY clause.

Example

The example below queries test scores for students in various grade levels. Unlike a regular MAX() aggregation, the window function allows us to see how each student individually compares to the highest test score for their grade level.

SELECT
	first_name,
	grade_level,
	test_score,
	MAX(test_score) OVER (PARTITION BY grade_level) AS highest_score
FROM
	class_test;

Returns:

+------------+-------------+------------+---------------+
| first_name | grade_level | test_score | highest_score |
+------------+-------------+------------+---------------+
| Frank      |           9 |         76 |            90 |
| Humphrey   |           9 |         90 |            90 |
| Iris       |           9 |         79 |            90 |
| Sammy      |           9 |         85 |            90 |
| Peter      |           9 |         80 |            90 |
| Jojo       |           9 |         78 |            90 |
| Brunhilda  |          12 |         92 |           100 |
| Franco     |          12 |         94 |           100 |
| Thomas     |          12 |         66 |           100 |
| Gary       |          12 |        100 |           100 |
| Charles    |          12 |         93 |           100 |
| Jesse      |          12 |         89 |           100 |
| Roseanna   |          11 |         94 |            94 |
| Carol      |          11 |         52 |            94 |
| Wanda      |          11 |         73 |            94 |
| Shangxiu   |          11 |         76 |            94 |
| Larry      |          11 |         68 |            94 |
| Otis       |          11 |         75 |            94 |
| Deborah    |          10 |         78 |            89 |
| Yolinda    |          10 |         30 |            89 |
| Albert     |          10 |         59 |            89 |
| Mary       |          10 |         85 |            89 |
| Shawn      |          10 |         89 |            89 |
+------------+-------------+------------+---------------+