Postgres max() function

You can use the Postgres max() function to find the maximum value in a set of values.

It's particularly useful for data analysis, reporting, and finding extreme values within datasets. You might use max() to find the product with the highest price in the catalog, the most recent timestamp in a log table, or the largest transaction amount in a financial system.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signature

The max() function has this simple form:

max(expression) -> same as expression

expression: Any valid expression that can be evaluated across a set of rows. This can be a column name or a function that returns a value.

Example usage

Consider an orders table that tracks orders placed by customers of an online store. It has columns order_id, customer_id, product_id, and order_date. We will use this table for examples throughout this guide.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER,
    order_amount DECIMAL(10, 2) NOT NULL,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (customer_id, product_id, order_amount, order_date)
VALUES
    (1, 101, 150.00, '2023-01-15 10:30:00'),
    (2, 102, 75.50, '2023-01-16 11:45:00'),
    (1, 103, 200.00, '2023-02-01 09:15:00'),
    (3, 104, 50.25, '2023-02-10 14:20:00'),
    (2, 105, 125.75, '2023-03-05 16:30:00'),
    (4, NULL, 90.00, '2023-03-10 13:00:00'),
    (1, 106, 180.50, '2023-04-02 11:10:00'),
    (3, 107, 60.25, '2023-04-15 10:45:00'),
    (5, 108, 110.00, '2023-05-01 15:20:00'),
    (2, 109, 95.75, '2023-05-20 12:30:00');

We can use max() to find the largest order amount:

SELECT max(order_amount) AS largest_order
FROM orders;

This query returns the following output:

largest_order
---------------
        200.00
(1 row)

To find the most recent order date, we compute the maximum value of order_date:

SELECT max(order_date) AS latest_order_date
FROM orders;

This query returns the following output:

latest_order_date
---------------------
 2023-05-20 12:30:00
(1 row)

Advanced examples

Using max() with GROUP BY

You can use max() with GROUP BY to find the maximum values in each group:

SELECT customer_id, max(order_amount) AS largest_order
FROM orders
GROUP BY customer_id
ORDER BY largest_order DESC
LIMIT 5;

This query finds the largest order amount for each customer and returns the top 5 customers, sorted in order of the largest order amount.

customer_id | largest_order
-------------+---------------
           1 |        200.00
           2 |        125.75
           5 |        110.00
           4 |         90.00
           3 |         60.25
(5 rows)

Using max() with a FILTER clause

The FILTER clause allows you to selectively include rows in the max() calculation:

SELECT
    max(order_amount) AS max_overall,
    max(order_amount) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 4) AS max_in_april
FROM orders;

This query calculates both the overall maximum order amount and the maximum order amount for the year 2023.

max_overall | max_in_april
-------------+--------------
      200.00 |       180.50
(1 row)

Finding the row with the maximum value for a column

To retrieve the entire row containing the maximum value, you can use a subquery:

SELECT *
FROM orders
WHERE order_amount = (SELECT max(order_amount) FROM orders);

This query returns the full details of the order with the maximum order_amount.

order_id | customer_id | product_id | order_amount |     order_date
----------+-------------+------------+--------------+---------------------
        3 |           1 |        103 |       200.00 | 2023-02-01 09:15:00
(1 row)

Using max() with window functions

max() can be used as a window function to calculate the running maximum over a set of rows:

SELECT
    order_id,
    order_date,
    max(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_max_amount
FROM orders
ORDER BY order_date;

This query calculates the running maximum order amount over time, showing how the largest order amount changes as new orders come in.

order_id |     order_date      | running_max_amount
----------+---------------------+--------------------
        1 | 2023-01-15 10:30:00 |             150.00
        2 | 2023-01-16 11:45:00 |             150.00
        3 | 2023-02-01 09:15:00 |             200.00
        4 | 2023-02-10 14:20:00 |             200.00
        5 | 2023-03-05 16:30:00 |             200.00
        6 | 2023-03-10 13:00:00 |             200.00
        7 | 2023-04-02 11:10:00 |             200.00
        8 | 2023-04-15 10:45:00 |             200.00
        9 | 2023-05-01 15:20:00 |             200.00
       10 | 2023-05-20 12:30:00 |             200.00
(10 rows)

Additional considerations

NULL values

max() ignores NULL values in its calculations. If all values in the set are NULL, max() returns NULL.

Performance implications

When used with an index on the column being evaluated, max() is typically very efficient. The database can often use an index scan to quickly find the maximum value without needing to examine every row in the table. For large datasets, ensure that the column used in the max() function is properly indexed to maintain good performance.

Alternative functions

min(): Returns the minimum value in a set of values.
greatest(): Returns the largest value from a list of values/expressions within a single row.

Resources

PostgreSQL documentation: Aggregate Functions