How to use Postgres Min() Function

In this PostgreSQL tutorial, I will show how to use the Postgres Min() Function with SELECT, GROUP BY, and HAVING clauses.

Additionally, you will understand how to use the min() function with subquery and multiple columns.

How to use Postgres Min() Function

The MIN() function of PostgreSQL finds the minimum value from the set of values, it is the aggregate function and returns the smallest value from the column or from the given set which contains the values.

The Postgres MIN() function takes the column with different data types such as integer, date and time, string and etc.

The syntax is given below.

MIN(exp)

Where exp is the expression in which you can pass any column or set of values for finding the minimum value.

The MIN() function in PostgreSQL is used with different clauses such as SELECT, HAVING, WHERE and etc.

Let’s see some of examples how to use Postgres MIN() function.

Postgres Min() Function using SELECT

The PostgreSQL MIN() can return the smallest value from the column using the SELECT statement, In other words, To get the least value from any column pass the column to the MIN() function.

Let’s say you have a table called ‘customers‘ with columns ‘customer_id’, ‘name’, ’email’, ‘age’, and ‘registration_date’.

Now find the least age of employee from all the employees in the table ‘customers’ using the below query.

SELECT MIN(age) FROM customers;

The above query returns the minimum age equal to 26 from the ‘age’ column of the table of ‘customers’. This is how you can find the smallest value from a column using the SELECT clause.

Postgres Min() Function using GROUP BY

You can use the MIN() function with a query containing GROUP BY to find the smallest value from each group.

Let’s take an example, suppose you have a table ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’ which is shown below.

Find the minimum purchase history of customers according to the countries or the countries with less purchase history using the below query.

SELECT 
	country,
	MIN(purchase_history) as min_purchase_history
FROM customers
GROUP BY country;

The above query returns the minimum purchase history of each country such as Canada, USA, Australia, and the UK has less purchase history of 70, 45, 55, and 27 respectively.

Postgres Min() Function using Subquery

The subquery is the query within another query and you can use the MIN() function with the subquery to get the minimum value and use the returned value from the subquery with the main query.

Let’s say you have a table ‘customers’ with the following data.

Now you want to find the customer with minimum purchase history in the country USA, use the below query.

SELECT
	first_name,
	country,
	purchase_history
FROM
	customers
WHERE
	purchase_history = (
		SELECT MIN(purchase_history)
                FROM customers
	);

Look at the above query part in the WHERE clause, the subquery is used to get the min purchase history of the customer from the table. The main query returns the customer’s first name, country, and purchase history based on the filter WHERE clause.

In the WHERE section of the query, the subquery returned the minimum purchase history equal to 0. Actually, you can think like this (WHERE purchase_history = 0), this zero is returned by the subquery.

As a result, the customer Michael who lives in the USA has zero purchase history. This is how you can use the Postgres Min() Function using Subquery.

Postgres Min() Function using More than one Column

If you want to find the least value from more than one column, the MIN() function isn’t sufficient or you can’t use it. Instead, a function called LEAST() in PostgreSQL return the least values from the multiple columns.

The reason you can’t use the MIN() to get the least values from multiple columns, it works on rows rather than columns. The LEAST() works on columns, so best to use it for finding the smallest values from the multiple columns.

For example, suppose you have the same table ‘customer’ with an extra column ‘items_purchased’.

Let’s find the customers with minimum purchase history and the item purchased using the below query.

SELECT
	first_name,
	purchase_history,
	items_purchased,
	LEAST (purchase_history, items_purchased) AS least_purchase_histoy_items
FROM
	customers
WHERE country = 'USA' AND purchase_history | items_purchased >0 ;

The query returns the minimum purchase history and the item purchased by customers Danny and Robert.

The LEAST function finds the minimum value between purchase history and items purchased for both customers that you can see in the output.

Basically, The LEAST function takes two columns and compares the value of the column, then returns the smallest value.

Here for the customer Danny, it compares the value of both columns and returns the minimum value as 3, and for Robert returns the minimum value as 6.

Postgres Min() Function using HAVING clause

The Postgres MIN() Function can use with the HAVING clause to filter the group based on the provided condition in the HAVING clause.

Let’s take an example using the same table ‘customers’ which is used in the above section. Suppose you need to find the customers with minimum items purchased greater than 10 for each country.

Use the below query.

 SELECT
 DISTINCT(country),
 first_name,
 MIN (items_purchased) 
 FROM
 customers
 GROUP BY
 country, 
 first_name 
 HAVING MIN(items_purchased) > 10;

The above query first returns the unique customers with the minimum of items purchased, then grouped by country and first name, after that, the result is filtered using the HAVING clause for items purchased greater than 10.

Conclusion

In this PostgreSQL tutorial, you have covered how to use the PostgreSQL min() function to find the least value from the table. Also learned about how to use this function using the SELECT, GROUP BY, HAVING, subquery, and LEAST() function.