Cloud

max

The max() window function computes the maximum value of an expression across a set of rows defined by a window specification.

Syntax

The syntax for this function is:

MAX ([ALL] expression) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
    RANGE BETWEEN start_value AND end_value
)

Parameters

  • ALL: Retains all duplicate values from the expression.

Examples

The following example uses a simplified version of the film table from the Pagila database, containing only the title, length and rating columns. The complete schema for the film table can be found on the Pagila database website.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating text
);
INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');

The query uses the max() function to find the maximum length of films for each rating category and also calculates a running maximum length as you move through the films ordered by length. The RunningMaxLength column updates as it encounters longer films.

SELECT
   title,
   length,
   rating,
   MAX(length) OVER ( PARTITION BY rating ) AS MaxLengthByRating,
   MAX(length) OVER (
ORDER BY
   length ROWS BETWEEN unbounded preceding AND CURRENT ROW ) AS RunningMaxLength
FROM film
ORDER BY length;

The query returns:

        title        | length | rating | maxlengthbyrating | runningmaxlength
---------------------+--------+--------+-------------------+------------------
 HALLOWEEN NUTS      |     47 | PG-13  |               143 |               47
 KILL BROTHERHOOD    |     54 | G      |               125 |               54
 PICKUP DRIVING      |     77 | G      |               125 |               77
 ATTRACTION NEWTON   |     83 | PG-13  |               143 |               83
 PRIVATE DROP        |    106 | PG     |               137 |              106
 DANGEROUS UPTOWN    |    121 | PG     |               137 |              121
 HOURS RAGE          |    122 | NC-17  |               179 |              122
 SAINTS BRIDE        |    125 | G      |               125 |              125
 FOREVER CANDIDATE   |    131 | NC-17  |               179 |              131
 PIANIST OUTFIELD    |    136 | NC-17  |               179 |              136
 SLEEPY JAPANESE     |    137 | PG     |               137 |              137
 MILLION ACE         |    142 | PG-13  |               143 |              142
 CLOCKWORK PARADISE  |    143 | PG-13  |               143 |              143
 CHRISTMAS MOONSHINE |    150 | NC-17  |               179 |              150
 INDEPENDENCE HOTEL  |    157 | NC-17  |               179 |              157
 WRATH MILE          |    176 | NC-17  |               179 |              176
 YOUTH KICK          |    179 | NC-17  |               179 |              179
(17 rows)