PostgreSQL MAX Aggregate Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL MAX aggregate function to find the maximum value in a set of values.

Getting Started with the PostgreSQL MAX Aggregate Function #

In PostgreSQL, the MAX aggregate function accepts a set of values and returns the maximum value.

The following shows how to use the MAX aggregate function to find the highest value in a column of a table:

SELECT
  MAX(column)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It’s important to note that the MAX function ignores NULL when determining the maximum value.

Finding Maximum Values for Groups #

To find the maximum values for each group, you can use the MAX function with the GROUP BY clause:

SELECT
  column1,
  MAX(column2)
FROM
  table_name
GROUP BY
  column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

The GROUP BY clause groups rows by the values in the column1 into groups.
The MAX function returns the maximum value in the column2 for each group.

This query allows you to gain insights from the data by analyzing groups and finding the highest value for each.

Using MAX with Expressions #

Besides a table column, you can use an expression in the MAX function:

SELECT
  MAX(expression)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the expression may involve table columns with operators.

PostgreSQL MAX Aggregate Function Examples #

Let’s explore some examples of using the MAX aggregate function.

Finding the Highest Price of All Products #

The following example uses the MAX function to find the highest price of all products:

SELECT
  MAX(price)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   max
---------
 2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the MAX function examines all values in the price column of the products table and returns the highest one.

To find the product with the highest price, you can use a subquery:

SELECT
  product_name,
  price
FROM
  products
WHERE
  price = (
    SELECT
      MAX(price)
    FROM
      products
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      product_name       |  price
-------------------------+---------
 Samsung QN900C Neo QLED | 2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

First, the subquery returns the highest price.
Second, the outer query finds the product whose price matches the highest price.

Note: The outer query will return more than one product if multiple products have the same highest price.

Finding Products with the Highest Prices in Each Category #

The following SELECT statement uses the MAX() aggregate function to find products with the highest price in each category:

SELECT
  category_id,
  MAX(price) max_price
FROM
  products
GROUP BY
  category_id
ORDER BY
  max_price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id | max_price
-------------+-----------
           5 |    249.99
           6 |    399.99
           4 |   1099.99
           9 |   1299.99
          12 |   1299.99
          11 |   1599.99
           3 |   1799.99
           8 |   2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

The GROUP BY clause groups the products by the category ID.
The MAX function returns the highest price for each group.

To retrieve the category name and highest price in each, you can join the products table with the categories table:

SELECT
  category_name,
  MAX(price) max_price
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  max_price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name | max_price
---------------+-----------
 Accessories   |    249.99
 Wearables     |    399.99
 Tablets       |   1099.99
 Audio Systems |   1299.99
 Desktops      |   1299.99
 Laptops       |   1599.99
 Smartphones   |   1799.99
 Televisions   |   2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the category with the highest price of less than 500, you can use the MAX in the HAVING clause:

SELECT
  category_name,
  MAX(price) max_price
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
HAVING
  MAX(price) < 500
ORDER BY
  max_price DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name | max_price
---------------+-----------
 Wearables     |    399.99
 Accessories   |    249.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You cannot use the max_price column alias in the HAVING clause because PostgreSQL evaluates the HAVING clause before the SELECT clause.

However, you can use the max_price column alias in the ORDER BY clause because PostgreSQL evaluates the ORDER BY clause after the SELECT clause.

Using the PostgreSQL MAX Aggregate Function with Dates #

The transactions table records the inventory transactions:

The following statement uses the MAX aggregate function to find the latest transaction date from the transactions table:

SELECT
  MAX(transaction_date)
FROM
  transactions;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

    max
------------
 2024-12-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To find the latest transaction in each warehouse, you can use the GROUP BY clause to group transactions by warehouse ID and the MAX function to find the latest transaction date in each:

SELECT
  warehouse_id,
  MAX(transaction_date) latest_transaction_date
FROM
  transactions
GROUP BY
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | latest_transaction_date
--------------+-------------------------
            3 | 2024-12-07
            2 | 2024-12-06
            1 | 2024-12-07Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL MAX Function with Expressions #

The following example uses the MAX function to find the highest inventory amount of all products:

SELECT
  MAX(quantity * price) highest_inventory_amount
FROM
  products
  JOIN inventories USING (product_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 highest_inventory_amount
--------------------------
                719997.60Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

First, calculate the inventory amount by multiplying the quantity in the inventory by the product price.
Second, use the MAX function to find the highest inventory amount.

To find the highest inventory amount by warehouse, you can group the inventory by warehouses:

SELECT
  warehouse_id,
  MAX(quantity * price) max_inventory_amount
FROM
  products
  JOIN inventories USING (product_id)
GROUP BY
  warehouse_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | max_inventory_amount
--------------+----------------------
            3 |            574997.70
            2 |            624997.50
            1 |            719997.60Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the MAX function to find the highest value in a set of values.

Quiz #

Was this tutorial helpful ?