MAX

9min
Overview
MAX() function returns the maximum value from a set of records.
The input and return types we support can be seen in the table below.
﻿
✅ The return will be the same as the data type used as the input values.
💡Special cases:
Returns NULL if there are no input rows or NULL values.
Returns NaN if the input contains a NaN.
Examples
For the MAX() examples, we will use the same sample table as in the MIN() section.
We have a movies table that stores the movie details, such as the movie’s title, category, and IMDb rating.
Create a Table
CREATE TABLE movies (
    movieid int,
    moviename string,
    moviecategory string,
    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);
CREATE TABLE movies (
    movieid int,
    moviename string,
    moviecategory string,
    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);
﻿
Display the Table
SELECT * FROM movies;
SELECT * FROM movies;
﻿
The above query will show the following table:
The movies Table
+---------+--------------------------+-----------------+-------------+
| 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           |
+---------+--------------------------+-----------------+-------------+
+---------+--------------------------+-----------------+-------------+
| 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           |
+---------+--------------------------+-----------------+-------------+
﻿
#Case 1: MAX() with a single expression
For example, you might want to know what is the highest rating among all stored movies:
Select max
SELECT MAX(imdbRating) AS "Highest Rating"
FROM movies;
SELECT MAX(imdbRating) AS "Highest Rating"
FROM movies;
﻿
It will return the following output:
Output
+-----------------+
| Highest Rating  |
+-----------------+
| 9.4             |
+-----------------+
+-----------------+
| Highest Rating  |
+-----------------+
| 9.4             |
+-----------------+
﻿
#Case 2: MAX() with GROUP BY clause
We use a MAX() for this example to get the highest rating in each movie category.
Select max
SELECT movieCategory AS "Movie Category", MAX(imdbRating) AS "Highest Rating"
FROM movies
GROUP BY movieCategory;
SELECT movieCategory AS "Movie Category", MAX(imdbRating) AS "Highest Rating"
FROM movies
GROUP BY movieCategory;
﻿
It will display the highest rating from a group of movieCategory as shown below:
Output
+------------------+-----------------+
| Movie Category   | Highest Rating  |
+------------------+-----------------+
| Thriller         | 8.5             |
| Romance          | 8.4             |
| Crime            | 9.3             |
| Horror           | 8.1             |
| Drama            | 9.4             |
| Action           | 9.2             |
| Science Fiction  | 9               |
+------------------+-----------------+
+------------------+-----------------+
| Movie Category   | Highest Rating  |
+------------------+-----------------+
| Thriller         | 8.5             |
| Romance          | 8.4             |
| Crime            | 9.3             |
| Horror           | 8.1             |
| Drama            | 9.4             |
| Action           | 9.2             |
| Science Fiction  | 9               |
+------------------+-----------------+
﻿
#Case 3: MAX() in a subquery
In this example, we want to get a movie that has the highest rating by using a subquery:
Select max
SELECT movieName, IMDbRating
FROM movies
WHERE IMDbRating = (
SELECT MAX(IMDbRating)
FROM movies
);
SELECT movieName, IMDbRating
FROM movies
WHERE IMDbRating = (
SELECT MAX(IMDbRating)
FROM movies
);
﻿
It will return the following result:
Output
+---------------------------+-------------+
| moviename                 | imdbrating  |
+---------------------------+-------------+
| The Shawshank Redemption  | 9.4         |
+---------------------------+-------------+
+---------------------------+-------------+
| moviename                 | imdbrating  |
+---------------------------+-------------+
| The Shawshank Redemption  | 9.4         |
+---------------------------+-------------+
﻿
﻿
Updated 09 Oct 2023
Did this page help you?
FOR_MIN
FOR_MAX
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: MAX() with a single expression
#Case 2: MAX() with GROUP BY clause
#Case 3: MAX() in a subquery