PostgreSQL offers various functions to compute and analyze the data. The MIN() and MAX() are built-in aggregate functions in Postgres that help us find the least and greatest values among the list of values. These functions can be applied to any data like strings, numbers, dates, etc. to quickly retrieve the minimum or maximum values.

This post will explain how to use the MIN() and MAX() functions to find the lowest and highest values among the given list of values.

How to Find/Get Maximum and Minimum Values From a PostgreSQL Table?

The MIN() and MAX() functions accept a set of values as arguments, compute the minimum and maximum of the given non-null values, and retrieve the least and greatest value among the given list respectively.

Utilize the given syntax to get the least value:

SELECT MIN(expression) 
FROM tab_name;

Use the provided syntax to find the maximum value:

SELECT MAX(expression) 
FROM tab_name;

The return type of the MIN() and MAX() functions depends on the argument type.

Example 1: Finding Minimum and Maximum Values From a Postgres Table

A table named “author_info” has already been created in our database. Let’s utilize the SELECT command with the “*” wildcard to fetch the table’s data:

SELECT * FROM author_info;

Let’s utilize the MIN() and MAX() functions on the “author_exp” column of the “author_info” table to find the authors having the least and most experience:

SELECT MIN(author_exp), MAX(author_exp)
FROM author_info;

The output shows that in the above table, the author’s minimum experience is “1 year” and maximum experience is “11 years”.

Let’s consider one more example for a profound understanding of the stated functions.

Example 2: Find Minimum and Maximum Experience for Each Gender

In the following example, we will utilize the MIN() and MAX() function to find the least and most experienced author. However, this time we will also utilize the GROUP BY clause to group the male and female authors separately:

SELECT gender, MIN(author_exp), MAX(author_exp)
FROM author_info
GROUP BY gender;

The output shows that the most experienced female author has eleven years of experience while a male author has seven years of experience. Similarly, the least experienced female has one year of experience and the least experienced male has two years.

Conclusion

In PostgreSQL, the MIN() and MAX() functions can be utilized to retrieve the minimum and maximum values among the list of given values. The MIN() and MAX() functions accept a set of values as arguments, compute the minimum and maximum of the given non-null values, and retrieve the least and greatest value among the given list respectively. This post has explained the use of MIN() and MAX() functions in Postgres.