Summary: in this tutorial, you will learn how to use PostgreSQLMIN() function to get the minimum value of a set.

Introduction to PostgreSQL MIN function

PostgreSQL MIN() 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 the MIN() function. The data type of the column can be numeric, string, or any comparable type.

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

MIN(expression)

Unlike the AVG(), COUNT() and SUM() functions, the DISTINCT option does not have any effects on the MIN() function.

PostgreSQL MIN() function examples

We will use the film , film_category, and category tables from the dvdrental sample database for demonstration.

film film_category category tables

1) Basic PostgreSQL MIN() function example

The following example uses the MIN() function to get the lowest rental rate from the rental_rate column the film 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 the MIN() function in a subquery to get the film information of the film with the lowest rental rate:

SELECT  film_id,  title,  rental_rate FROM  film WHERE  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, the subquery 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 the MIN function with the GROUP BY clause to find the lowest value in each group.

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

SELECT  name category,  MIN(replacement_cost) replacement_cost FROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id) GROUP BY  name ORDER 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 the MIN function in the HAVING clause the filter of the groups whose minimum values meet a specific condition.

The following query uses the MIN() function to find the lowest replacement costs of films grouped by category, selecting only groups with replacement costs greater than 9.99:

SELECT  name category,  MIN(replacement_cost) replacement_cost FROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id) GROUP BY  name HAVING  MIN(replacement_cost) > 9.99 ORDER 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 as MAX() function in the same query.

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

SELECT  name category,  MIN(length) min_length,  MAX(length) max_length FROM  category  INNER JOIN film_category USING (category_id)  INNER JOIN film USING (film_id) GROUP BY  name ORDER 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 the MIN() function to find the lowest value in a set of values.
  • Use the MIN() with GROUP BY clause to find the lowest value in a group of values.