---
title: Postgres max() function
subtitle: Find the maximum value in a set of values
summary: >-
  Covers the usage of the Postgres `max()` function to determine the maximum
  value in a dataset, including examples for finding the largest order amount
  and the most recent order date in an orders table.
enableTableOfContents: true
updatedOn: '2026-02-06T22:07:32.880Z'
---

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.

<CTA />

## Function signature

The `max()` function has this simple form:

```sql
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.

```sql
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:

```sql
SELECT max(order_amount) AS largest_order
FROM orders;
```

This query returns the following output:

```text
 largest_order
---------------
        200.00
(1 row)
```

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

```sql
SELECT max(order_date) AS latest_order_date
FROM orders;
```

This query returns the following output:

```text
  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:

```sql
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.

```text
 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:

```sql
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.

```text
 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:

```sql
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`.

```text
 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:

```sql
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.

```text
 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](https://www.postgresql.org/docs/current/functions-aggregate.html)
