PostgreSQL MAX Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL MAX() window function get retrieve the maximum value in a set of values within a partition.

Getting Started with the PostgreSQL MAX Window Function #

In PostgreSQL, the MAX() window function returns the maximum value in a set of values within a partition.

Here’s the syntax of the MAX() window function:

MAX(value) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Parameters:

• value: A column or expression you want to get the maximum value from.
• OVER: Defines the window frame where the MAX function applies.
• PARTITION BY: Divides the result sets into partitions by the partition_expression. The partition_expression can be one or more columns. If you omit the PARTITION BY clause, the MAX() function treats the whole result set as a single partition.
• ORDER BY: Determines the order of rows within each partition.

PostgreSQL MAX Window Function Examples #

Let’s take some examples of using the MAX window function with the products table:

Finding the Highest Product Price #

The following SELECT statement uses the MAX() window function to retrieve the highest price along with the product information of the current row:

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

Try it

Output:

        product_name        |  price  | max_price
----------------------------+---------+-----------
 Samsung QN900C Neo QLED    | 2999.99 |   2999.99
 LG G3 OLED                 | 2499.99 |   2999.99
 Sony Bravia XR A95K        | 2499.99 |   2999.99
 LG OLED TV C3              | 1999.99 |   2999.99
 Samsung Galaxy Z Fold 5    | 1799.99 |   2999.99
 Lenovo ThinkPad X1 Carbon  | 1599.99 |   2999.99
 Dell XPS 15                | 1499.99 |   2999.99
 HP Spectre x360            | 1399.99 |   2999.99
 Microsoft Surface Laptop 5 | 1299.99 |   2999.99
 Apple iPhone 15 Pro Max    | 1299.99 |   2999.99
 Sony HT-A7000 Soundbar     | 1299.99 |   2999.99
 Apple iMac 24"             | 1299.99 |   2999.99
 Apple iPhone 15            | 1099.99 |   2999.99
 Apple iPad Pro 12.9        | 1099.99 |   2999.99
 Samsung Galaxy S24         |  999.99 |   2999.99
 Dell Inspiron 27           |  999.99 |   2999.99
 Sony Xperia 1 VI           |  949.99 |   2999.99
 Huawei Mate 60             |  899.99 |   2999.99
 Xiaomi Mi 14               |  799.99 |   2999.99
 Samsung Galaxy Tab S9      |  699.99 |   2999.99
 Apple Watch Series 9       |  399.99 |   2999.99
 Bose SoundLink Max         |  399.99 |   2999.99
 Samsung Galaxy Watch 6     |  349.99 |   2999.99
 Apple AirPods Pro 3        |  249.99 |   2999.99
 Samsung Galaxy Buds Pro 2  |  199.99 |   2999.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the MAX() function treats the whole result set as a single partition because there is no PARTITION BY clause and returns the product’s highest price along with the information of the current row.

Finding the Highest Price for Each Brand #

The following SELECT statement uses the MAX window function to retrieve the highest product price of the product in each brand:

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

Try it

How it works:

1. The PARTITION BY clause divides the rows by brand_id into partitions (or brands).
2. The MAX() window function returns the highest price for each brand.

To retrieve the brand names, you can join the products table with the brands table:

SELECT
  brand_name,
  product_name,
  price,
  MAX(price) OVER (
    PARTITION BY brand_id
  ) max_price
FROM
  products
  JOIN brands USING (brand_id)
ORDER BY
  brand_name,
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

brand_name |        product_name        |  price  | max_price
------------+----------------------------+---------+-----------
 Apple      | Apple AirPods Pro 3        |  249.99 |   1299.99
 Apple      | Apple Watch Series 9       |  399.99 |   1299.99
 Apple      | Apple iPhone 15            | 1099.99 |   1299.99
 Apple      | Apple iPad Pro 12.9        | 1099.99 |   1299.99
 Apple      | Apple iMac 24"             | 1299.99 |   1299.99
 Apple      | Apple iPhone 15 Pro Max    | 1299.99 |   1299.99
 Dell       | Bose SoundLink Max         |  399.99 |   1999.99
 Dell       | Dell Inspiron 27           |  999.99 |   1999.99
 Dell       | Dell XPS 15                | 1499.99 |   1999.99
 Dell       | LG OLED TV C3              | 1999.99 |   1999.99
 HP         | HP Spectre x360            | 1399.99 |   1399.99
 Huawei     | Huawei Mate 60             |  899.99 |    899.99
 Lenovo     | Lenovo ThinkPad X1 Carbon  | 1599.99 |   1599.99
 LG         | LG G3 OLED                 | 2499.99 |   2499.99
 Microsoft  | Microsoft Surface Laptop 5 | 1299.99 |   1299.99
 Samsung    | Samsung Galaxy Buds Pro 2  |  199.99 |   2999.99
 Samsung    | Samsung Galaxy Watch 6     |  349.99 |   2999.99
 Samsung    | Samsung Galaxy Tab S9      |  699.99 |   2999.99
 Samsung    | Samsung Galaxy S24         |  999.99 |   2999.99
 Samsung    | Samsung Galaxy Z Fold 5    | 1799.99 |   2999.99
 Samsung    | Samsung QN900C Neo QLED    | 2999.99 |   2999.99
 Sony       | Sony Xperia 1 VI           |  949.99 |   2499.99
 Sony       | Sony HT-A7000 Soundbar     | 1299.99 |   2499.99
 Sony       | Sony Bravia XR A95K        | 2499.99 |   2499.99
 Xiaomi     | Xiaomi Mi 14               |  799.99 |    799.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the MAX window function to retrieve the maximum value in a set of values within a partition.

Quiz #