Summary: this tutorial shows you how to use the PostgreSQL MAX function to get the maximum value of a set.
Introduction to PostgreSQL MAX function
PostgreSQL MAX
function is an aggregate function that returns the maximum value in a set of values. The MAX
function is useful in many cases. For example, to find the employees with the highest salary, or to find the most expensive products, etc.
The syntax of the MAX
function is as follows:
1 | MAX(expression); |
You can use the MAX
function not only in the SELECT
clause but also in the WHERE
and HAVING
clauses. Let’s take a look at some examples of using the MAX
function.
PostgreSQL MAX function examples
Let’s examine the payment
table in the sample database.
The following query gets the maximum amount paid by customers in the payment
table:
1 2 | SELECT MAX(amount) FROM payment; |
PostgreSQL MAX function in subquery
To get other information together with the maximum payment, you use a subquery as follows:
1 2 3 4 5 6 7 8 9 10 11 | SELECT * FROM payment WHERE amount = ( SELECT MAX (amount) FROM payment ); |
First, the subquery gets the maximum payment and then the outer query selects all rows with the amount that is equal to the maximum payment returned from the subquery. The output of the query is as follows:
The following diagram illustrates the steps that PostgreSQL performs the query:
PostgreSQL MAX function with GROUP BY clause
You can combine the MAX
function with GROUP BY clause to get the maximum value per group. For example, the following query gets the largest payment paid by each customer.
1 2 3 4 5 6 7 | SELECT customer_id, MAX (amount) FROM payment GROUP BY customer_id; |
PostgreSQL MAX function with HAVING clause
If you use the MAX
function in a HAVING
clause, you can apply a filter for the whole group. For example, the following query selects only the biggest payment paid by each customer and the biggest payments are greater than 8.99
.
1 2 3 4 5 6 7 8 | SELECT customer_id, MAX (amount) FROM payment GROUP BY customer_id HAVING MAX(amount) > 8.99 |
Finding largest values from two or more columns
Let’s take a look at an example.
First, create a new table named ranks
for the demonstration. It has four columns: 1 column to store user id and other three columns to store rank from 1 to 3.
1 2 3 4 5 6 | CREATE TABLE IF NOT EXISTS ranks ( user_id INT PRIMARY KEY, rank_1 int4 NOT NULL, rank_2 int4 NOT NULL, rank_3 int4 NOT NULL ); |
Second, insert sample data into the ranks
table as follows:
1 2 3 4 5 | INSERT INTO ranks VALUES (1, 6, 3, 5), (2, 2, 8, 5), (3, 5, 9, 8); |
How do you get the largest rank per user as shown in the screenshot below:
To achieve this, you use GREATEST
function instead of MAX
function. The GREATEST
function returns the greatest value from a list of values.
1 2 3 4 5 | SELECT user_id, GREATEST (rank_1, rank_2, rank_3) AS largest_rank FROM ranks; |
In this tutorial, you have learned how to use the PostgreSQL MAX
function to find the maximum value of a set.