Introduction to PostgreSQL MIN function

PostgreSQLMIN() function is an aggregate function that returns the minimum value in a set of values.

To find the minimum value in a column of a table, you pass the column name theMIN() function. Thedata type of the column can benumeric,string, or any comparable type.

Here’s the basic syntax of theMIN() function:

MIN(expression)

Unlike theAVG(),COUNT() andSUM() functions, theDISTINCT option does not have any effects on theMIN() function.

PostgreSQL MIN() function examples

We will use thefilm ,film_category, andcategory tables from thedvdrental sample database for demonstration.

1) Basic PostgreSQL MIN() function example

The following example uses theMIN() function to get the lowest rental rate from therental_rate column thefilm table:

SELECT   MIN (rental_rate)FROM   film;

Output:

min------ 0.99(1 row)

The query returns 0.99, which is the lowest rental rate.

2) Using the PostgreSQL MIN() function in a subquery example

The following example uses theMIN() function in a subquery to get the film information of the film with the lowest rental rate:

SELECT  film_id,  title,  rental_rateFROM  filmWHERE  rental_rate= (    SELECT      MIN(rental_rate)    FROM      film  );

Output:

film_id |          title          | rental_rate---------+-------------------------+-------------       1 | Academy Dinosaur        |        0.99      11 | Alamo Videotape         |        0.99      12 | Alaska Phantom          |        0.99     213 | Date Speed              |        0.99...

How it works.

• First, thesubquery to select the lowest rental rate.
• Then, the outer query selects films with rental rates equal to the lowest rental rate returned by the subquery.

3) Using PostgreSQL MIN() function with GROUP BY clause example

In practice, you often use theMIN function with theGROUP BY clause to find the lowest value in each group.

The following statement uses theMIN() function with theGROUP BY clause to find the lowest replacement cost of films by category:

SELECT  name category,  MIN(replacement_cost) replacement_costFROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id)GROUP BY  nameORDER BY  name;

Output:

category   | replacement_cost-------------+------------------ Action      |             9.99 Animation   |             9.99 Children    |             9.99 Classics    |            10.99 Comedy      |             9.99...

4) Using PostgreSQL MIN() function with the HAVING clause example

It’s possible to use theMIN function in theHAVING clause the filter of the groups whose minimum values meet a specific condition.

The following query uses theMIN() function to find the lowest replacement costs of films grouped by category, selecting only groups with replacement costs greater than9.99:

SELECT  name category,  MIN(replacement_cost) replacement_costFROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id)GROUP BY  nameHAVING  MIN(replacement_cost)> 9.99ORDER BY  name;

Output:

category | replacement_cost----------+------------------ Classics |            10.99 Horror   |            10.99 Music    |            10.99(3 rows)

5) Using the PostgreSQL MIN() function with other aggregate functions example

It’s possible to use the MIN() function with other aggregate functions such asMAX() function in the same query.

The following example uses theMIN() andMAX() function to find the shortest and longest films by category:

SELECT  name category,  MIN(length) min_length,  MAX(length) max_lengthFROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id)GROUP BY  nameORDER BY  name;

Output:

category   | min_length | max_length-------------+------------+------------ Action      |         47 |        185 Animation   |         49 |        185 Children    |         46 |        178 Classics    |         46 |        184 Comedy      |         47 |        185 Documentary |         47 |        183 Drama       |         46 |        181 Family      |         48 |        184 Foreign     |         46 |        184 Games       |         57 |        185 Horror      |         48 |        181 Music       |         47 |        185 New         |         46 |        183 Sci-Fi      |         51 |        185 Sports      |         47 |        184 Travel      |         47 |        185(16 rows)

Summary

• Use theMIN() function to find the lowest value in a set of values.
• Use theMIN() withGROUP BY clause to find the lowest value in a group of values.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL MIN function
• PostgreSQL MIN() function examples