PostgreSQL FIRST_VALUE Window Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL FIRST_VALUE window function to retrieve the first value in an ordered set of values.

Getting Started with the FIRST_VALUE Window Function #

The FIRST_VALUE is a window function that allows you to retrieve the first value in an ordered set of values.

Syntax #

Here’s the basic syntax of the FIRST_VALUE function:

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

Parameters: #

expression: The column or expression from which you want to retrieve the first value.
PARTITION BY: Divides the result set into partitions by the partition_expression. The partition_expression can be one or more columns you want to use to divide the result set.
ORDER BY: Determines the order of rows within each partition using the sort_expression. The sort_expression can be one or more columns used for sorting the rows within each partition.

Examples of PostgreSQL FIRST_VALUE Window Function #

Let’s explore examples of using the FIRST_VALUE function with the products table:

PostgreSQL FIRST_VALUE Window Function - Sample Table

Finding the First Product with the Highest Price #

The following SELECT statement uses the FIRST_VALUE function to find the product with the highest price:

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

Try it

Output:

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

In this example:

The FIRST_VALUE function does not use the PARTITION BY clause. Therefore, it treats the whole result set as a partition.
The ORDER BY price DESC sorts the rows in the partition by price from high to low.
The FIRST_VALUE retrieves the product name of the first product in the result set, which is the product with the highest price.

Finding the Most Expensive Product in Each Category #

The following SELECT statement uses the FIRST_VALUE function to find the product in each category with the highest price:

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

Try it

Output:

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

In this example:

The PARTITION BY clause divides the rows from the products table into partitions by category_id.
The ORDER BY clause sorts the products in each partition by the price in descending order.
The FIRST_VALUE function retrieves the name of the first product in each category based on the highest price.

Summary #

Use the PostgreSQL FIRST_VALUE function to get the first value in a set of values.

Quiz #

Was this tutorial helpful ?