Overview
MIN()
is a function that returns the minimum value from a set of records.
Syntax
The syntax for this function is as follows:
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.
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);
By running the above query, we will get the following output:
+---------+--------------------------+-----------------+-------------+
| 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 |
+---------+--------------------------+-----------------+-------------+
MIN()
with a single expression
For example, you might want to know what is the lowest rating of all stored movies:
SELECT MIN(imdbRating) AS "Lowest Rating"
FROM movies;
+----------------+
| Lowest Rating |
+----------------+
| 7.8 |
+----------------+
MIN()
with GROUP BY
clause
In this example, we will use a GROUP BY
clause to group the movie categories, then use MIN()
function to get the lowest rating in each movie category and arrange the results in ascending order.
SELECT
movieCategory AS "Movie Category",
MIN(imdbRating) AS "Lowest Rating"
FROM movies
GROUP BY movieCategory
ORDER BY MIN(imdbRating) ASC;
By running the code above, we will get the following output:
Movie Category | Lowest Rating
-----------------+---------------
Romance | 7.8
Drama | 8
Horror | 8.1
Action | 8.1
Thriller | 8.5
Science Fiction | 9
Crime | 9.3
(7 rows)