In this tutorial, you will learn how to use PostgreSQL MIN function to get the minimum value of an expression.
Introduction to PostgreSQL MIN function
PostgreSQL MIN function is an aggregate function that gets the minimum value of an expression. You use SELECT statement and pass a column to the MIN function, it returns the row(s) whose value of the column is the smallest. The data type of the column can be number, character, or any comparable data type.
The syntax of the MIN function is as follows:
1 | MIN(expression) |
Or in a SELECT statement:
1 2 | SELECT MIN(column) FROM table; |
PostgreSQL MIN function examples
Let’s take a look at some examples of using the MIN function.
PostgreSQL MIN function in SELECT clause
We will use the film table in the dvdrental sample database for demonstration.
You often use the MIN function in the SELECT clause. For example, to get the minimum rental rate of the film in the film table, you use the following query:
1 2 3 4 | SELECT MIN (rental_rate) FROM film; |
The query returns 0.99, which is the minimum rental rate.
PostgreSQL MIN function in a subquery
To get films which have minimum rental rate, you use the following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT film_id, title, rental_rate FROM film WHERE rental_rate = ( SELECT MIN (rental_rate) FROM film ); |
In the above query, we used a subquery to select the minimum rental rate. In the outer query, we selected all films which have rental rate equals to the minimum rental rate returned from the subquery. The following diagram illustrates the steps that PostgreSQL performed the query:
PostgreSQL MIN function with GROUP BY clause
You can use the MIN function in the query that uses GROUP BY clause to find the minimum value per group for all groups. For example, for each group of films that have the same rating, the following query finds the minimum rental rate.
1 2 3 4 5 6 7 8 9 | SELECT rating, MIN (rental_rate) FROM film GROUP BY rating ORDER BY rating; |
PostgreSQL MIN function with HAVING clause
You can use the MIN function not only in the SELECT clause but also in the HAVING clause the filter the groups whose minimum values match a certain condition. For example, the following query finds the minimum replacement costs of films grouped by rating and selects only rating that has replacement cost greater than 9.
1 2 3 4 5 6 7 8 | SELECT rating, MIN (replacement_cost) FROM film GROUP BY rating HAVING MIN (replacement_cost) > 9 |
PostgreSQL MIN function with other aggregate functions
You can combine the MIN function with the MAX function in the same query to find the minimum and maximum values per group. For example, for each group of films that have the same rating, the following query selects the minimum and maximum rental rates.
1 2 3 4 5 6 7 8 | SELECT rating, MIN (rental_rate), MAX (rental_rate) FROM film GROUP BY rating; |
Finding smallest values from two or more columns
Suppose, you have the following ranks table:
1 2 3 4 5 6 | CREATE TABLE ranks ( user_id INT PRIMARY KEY, rank_1 int4 NOT NULL, rank_2 int4 NOT NULL, rank_3 int4 NOT NULL ); |
And its sample data:
1 2 3 4 5 | INSERT INTO ranks VALUES (1, 6, 3, 5), (2, 2, 8, 5), (3, 5, 9, 8); |
Now the requirement is that for each user, find its smallest possible rank as follows:
You cannot use the MIN function because the MIN function is applied to rows, not columns. To do this, you use LEAST function that returns the smallest value from a list of values.
1 2 3 4 5 | SELECT user_id, LEAST (rank_1, rank_2, rank_3) AS smallest_rank FROM ranks; |
The query returns the result as we expected.
In this tutorial, we have shown you how to use the PostgreSQL MIN function get minimum value of expression.