> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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:

```sql theme={null}
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
<a href="https://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/pagila/pagila/" target="_blank">Pagila</a> database website.

```sql theme={null}
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.

```sql theme={null}
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:

```sql theme={null}
        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)
```