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

`MAX()` is a function that returns the maximum value from a set of records.

## Syntax

The syntax for this function is as follows:

```sql theme={null}
MAX(column_name)
```

This function's output data type will always be the same as the input one, however it returns `NULL` if there are no records or input consists of `NULL` values and it also returns `NaN` if the input contains a `NaN`.

## Examples

For the needs of this section, we will create a movies table that stores movie details, such as movie's title, category, and IMDb rating.

```sql theme={null}
CREATE TABLE movies (
    movieid int,
    moviename text,
    moviecategory text,
    imdbrating real
);
INSERT INTO movies (movieid, moviename, moviecategory, imdbrating)
VALUES
(8557411, 'The Shawshank Redemption', 'Drama', 9.4),
(8557421, 'Life Is Beautiful', 'Romance', 8.4),
(8557451, 'The Godfather', 'Crime', 9.3),
(8557311, 'Prisoners', 'Thriller', 8.5),
(8557321, 'Inception', 'Science Fiction', 9),
(8557351, 'The Dark Knight', 'Action', 9.2),
(8557221, 'Coco', 'Drama', 8.2),
(8557251, 'The Sixth Sense', 'Horror', 8.1),
(8557231, 'Kill Bill: Vol. 1', 'Action', 8.1),
(8557281, 'The Notebook', 'Romance', 7.8),
(8557291, 'Forrest Gump', 'Drama', 8);
```

```sql theme={null}
SELECT * FROM movies;
```

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

```sql theme={null}
+---------+--------------------------+-----------------+-------------+
| movieid | moviename                | moviecategory   | imdbrating  |
+---------+--------------------------+-----------------+-------------+
| 8557411 | The Shawshank Redemption | Drama           | 9.4         |
| 8557421 | Life Is Beautiful        | Romance         | 8.4         |
| 8557451 | The Godfather            | Crime           | 9.3         |
| 8557311 | Prisoners                | Thriller        | 8.5         |
| 8557321 | Inception                | Science Fiction | 9           |
| 8557351 | The Dark Knight          | Action          | 9.2         |
| 8557221 | Coco                     | Drama           | 8.2         |
| 8557251 | The Sixth Sense          | Horror          | 8.1         |
| 8557231 | Kill Bill: Vol. 1        | Action          | 8.1         |
| 8557281 | The Notebook             | Romance         | 7.8         |
| 8557291 | Forrest Gump             | Drama           | 8           |
+---------+--------------------------+-----------------+-------------+
```

### `MAX()` with a single expression

For example, you might want to know what is the highest rating among all stored movies:

```sql theme={null}
SELECT MAX(imdbRating) AS "Highest Rating"
FROM movies;
```

```sql theme={null}
+-----------------+
| Highest Rating  |
+-----------------+
| 9.4             |
+-----------------+
```

### `MAX()` with GROUP BY clause

We use a `MAX()` function in this example to get the highest rating in each movie category and the results are ordered by the rating in ascending order.

```sql theme={null}
SELECT
  movieCategory AS "Movie Category",
  MAX(imdbRating) AS "Highest Rating"
FROM movies
GROUP BY movieCategory
ORDER BY MAX(imdbRating) ASC;
```

By running the above code, we will get the highest rating from a group of `movieCategory` as shown below:

```bash theme={null}
 Movie Category  | Highest Rating 
-----------------+----------------
 Horror          |            8.1
 Romance         |            8.4
 Thriller        |            8.5
 Science Fiction |              9
 Action          |            9.2
 Crime           |            9.3
 Drama           |            9.4
(7 rows)
```