Cloud

max

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

Syntax

The syntax for this function is:

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

The following examples use a movies table that stores movie details, such as movie’s title, category, and IMDb rating.

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);
SELECT * FROM movies;

The query returns:

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

SELECT MAX(imdbRating) AS "Highest Rating"
FROM movies;
+-----------------+
| Highest Rating  |
+-----------------+
| 9.4             |
+-----------------+

max() with GROUP BY clause

This example uses a max() function to get the highest rating in each movie category and the results are ordered by the rating in ascending order.

SELECT
  movieCategory AS "Movie Category",
  MAX(imdbRating) AS "Highest Rating"
FROM movies
GROUP BY movieCategory
ORDER BY MAX(imdbRating) ASC;

This returns the highest rating from a group of movieCategory:

 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)