Postgres Min Function

In this PostgreSQL tutorial, I will show you how to use the Postgres Min Function with SELECT, GROUP BY, and HAVING clauses. You will also understand how to use the min() function with a subquery and multiple columns.

Postgres Min Function

PostgreSQL’s MIN() function finds the minimum value from the set of values. It is an aggregate function that returns the smallest value from the column or the given set containing the values.

The Postgres MIN() function takes columns with different data types, such as integers, dates and times, strings, etc.

Syntax

The syntax is given below.

MIN(exp)

Where exp is the expression where you can pass any column or set of values to find the minimum value.

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

Let’s see some approaches to using the Postgres MIN() function.

Approach-1 Using SELECT

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

Example

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

Using the below query, find the employee with the lowest age among all the employees in the table ‘customers’.

SELECT MIN(age) FROM customers;

After executing the above query, I got the expected output as shown in the screenshot below.

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

Approach-2 Using GROUP BY

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

Example

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

Using the below query, find the minimum purchase history of customers according to the countries with the least purchase history.

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

After executing the above query, I got the expected output as shown in the screenshot below.

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

Approach-3 Using Subquery

The subquery is a query within another query. 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.

Example

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

Use the query below to find the customer with the minimum purchase history in the USA.

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

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the above query in the WHERE clause. The subquery is used to get the customer’s minimum purchase history 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. You can think like this (WHERE purchase_history = 0), this zero is returned by the subquery.

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

Approach-4 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 returns the least value from multiple columns.

You can’t use the MIN() to get the fewest values from multiple columns because it works on rows rather than columns. The LEAST() works on columns, so it’s best to use it to find the smallest values from multiple columns.

Example

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

Using the query below, let’s find the customers with the minimum purchase history and the items purchased.

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 ;

After executing the above query, I got the expected output as shown in the screenshot below.

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

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

The LEAST function takes two columns, compares their values, and returns the smallest value.

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

Approach-5 Using the HAVING clause

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

Example

Let’s take an example using the same table, ‘customers,’ as in the above section. Suppose you need to find the customers who have made a minimum purchase of more than 10 items 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;

After executing the above query, I got the expected output as shown in the screenshot below.

The above query first returns the unique customers with the minimum number of items purchased, then groups them by country and first name. The result is then 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() functions.

You may also like the following articles.

• PostgreSQL RANK Function
• Postgres Length Function
• PostgreSQL DATE Functions with Examples
• Postgresql now() function