MAX()

Overview

The MAX() window function is used to compute the maximum value of an expression across a set of rows defined by a window specification.

Syntax

The syntax for this function is as follows:

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

Example

For the needs of this section, we will use 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 below uses the MAX() to find the maximum length of films for each rating category and also calculate a running maximum length as you move through the films ordered by length. The RunningMaxLength column will be updated 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;

By running the above code, we will get the following output:

        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)