PostgreSQL MIN Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL MIN() window function to retrieve the minimum value in a set of values along with the current row.

Getting Started with the PostgreSQL MIN Window Function #

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

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

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

In this syntax:

• value: A column or expression you want to get the minimum value.
• OVER: Defines the window frame where the MIN() 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 MIN function treats the whole result set as a single partition.
• ORDER BY: Determines the order of rows within each partition.

PostgreSQL MIN Window Function Examples #

Let’s explore practical examples using the MIN() window function with the products table:

Finding the Lowest Product Price #

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

SELECT
  product_name,
  price,
  MIN(price) OVER () AS min_price
FROM
  products
ORDER BY
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

In this example, the MIN() function treats the whole result set as a single partition because of the absence of a PARTITION BY clause, and it returns the lowest price in the products table.

Finding the Lowest Product Price in Each Category #

The following SELECT statement uses the MIN() window function to retrieve the lowest product price of the product in each category:

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

Try it

Output:

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

How it works

• The PARTITION BY clause divides the rows by category_id into partitions (or categories).
• The MIN() window function returns the lowest price within each category.

To retrieve the category name, you can join the products table with the categories table:

SELECT
  category_name,
  product_name,
  price,
  MIN(price) OVER (
    PARTITION BY category_id
  ) AS min_price
FROM
  products
  JOIN categories USING (category_id)
ORDER BY
  category_name,
  price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

Summary #

• Use the MIN() window function to retrieve the minimum value in a set of values within a partition.

Quiz #