Introduction to PostgreSQL MAX() function

PostgreSQLMAX() function is an aggregate function that returns the maximum value in a set of values.

TheMAX() function can be useful in many cases. For example, you can use it to find the employees with the highest salary or to identify the most expensive products.

Here’s the syntax of the MAX function:

MAX(expression);

You can use the MAX() function not just in theSELECT clause but also in theWHERE andHAVING clauses.

PostgreSQL MAX() function examples

Let’s take some examples of using the MAX() function. We’ll use thepayment table from thesample database.

1) Basic PostgreSQL MAX() function example

The following query uses theMAX() function to find the highest amount paid by customers in thepayment table:

SELECT  MAX(amount)FROM  payment;

Output:

max------- 11.99(1 row)

2) Using the PostgreSQL MAX() function in subquery

The following example uses theMAX() function in a subquery to get the detailed payment information:

SELECT  payment_id,  customer_id,  amountFROM  paymentWHERE  amount = (    SELECT      MAX (amount)    FROM      payment  );

Output:

payment_id | customer_id | amount------------+-------------+--------      20403 |         362 |  11.99      22650 |         204 |  11.99      23757 |         116 |  11.99...

How it works.

• First, the subquery uses theMAX() function to return the highest payment.
• Second, the outer query retrieves all the payments whose amounts are equal to the highest payment returned from the subquery.

3) Using PostgreSQL MAX() function with the GROUP BY clause

You can combine theMAXfunction with theGROUP BY clause to get the maximum value for each group.

The following example uses theMAX() function with aGROUP BY clause to retrieve the highest payment paid by each customer.

SELECT  customer_id,  MAX (amount)FROM  paymentGROUP BY  customer_id;

Output:

customer_id |  max-------------+-------         184 |  9.99          87 | 10.99         477 | 10.99         273 |  8.99

4) Using PostgreSQL MAX() function with a HAVING clause

If you use theMAX() function in aHAVING clause, you can apply a filter for a group. For example, the following query uses theMAX() function to select the highest payment made by each customer and includes those that are over8.99:

SELECT  customer_id,  MAX (amount)FROM  paymentGROUP BY  customer_idHAVING  MAX(amount)> 8.99;

Output:

customer_id |  max-------------+-------         184 |  9.99          87 | 10.99         477 | 10.99         550 | 10.99          51 |  9.99...

Summary

• Use the PostgreSQLMAX() function to find the maximum value of a set.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL MAX() function
• PostgreSQL MAX() function examples
• Summary