PostgreSQL LAST_VALUE Window Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL LAST_VALUE() window function to retrieve the last value in an ordered set of values

Getting Started with the PostgreSQL LAST_VALUE Window Function #

In PostgreSQL, the LAST_VALUE() is a window function that allows you to retrieve the last value in an ordered set of values.

Here’s the basic syntax of the LAST_VALUE function:

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

In this syntax:

expression: This is a column or expression from which you want to retrieve the last value.
PARTITION BY: This clause divides the result set into partitions by the partition_expression. The partition_expression can be one or more columns used to divide the result set.
ORDER BY: This clause 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.
The frame_clause define a window in each partition where the LAST_VALUE function applies.

Examples of PostgreSQL LAST_VALUE Window Function #

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

PostgreSQL LAST_VALUE Window Function - Sample Table

Finding the Last Product with the Lowest Price #

The following SELECT statement uses the LAST_VALUE function to retrieve the product with the lowest price:

SELECT
  product_name,
  price,
  LAST_VALUE(product_name) OVER (
    ORDER BY price DESC 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

In this example:

The LAST_VALUE function does not use the PARTITION BY clause, thus treating 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 LAST_VALUE retrieves the product name of the last product in the result set, which is the product with the highest price.
The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING defines a frame where the LAST_VALUE function applies, starting at the partition’s first row and ending at the last row.

Finding the Products with the Lowest Price in Each Category #

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

SELECT
  category_id,
  product_name,
  price,
  LAST_VALUE(product_name) OVER (
    PARTITION BY category_id
    ORDER BY price DESC 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id |        product_name        |  price  |         last_value
-------------+----------------------------+---------+----------------------------
           3 | Samsung Galaxy Z Fold 5    | 1799.99 | Xiaomi Mi 14
           3 | Apple iPhone 15 Pro Max    | 1299.99 | Xiaomi Mi 14
           3 | Apple iPhone 15            | 1099.99 | Xiaomi Mi 14
           3 | Samsung Galaxy S24         |  999.99 | Xiaomi Mi 14
           3 | Sony Xperia 1 VI           |  949.99 | Xiaomi Mi 14
           3 | Huawei Mate 60             |  899.99 | Xiaomi Mi 14
           3 | Xiaomi Mi 14               |  799.99 | Xiaomi Mi 14
           4 | Apple iPad Pro 12.9        | 1099.99 | Samsung Galaxy Tab S9
           4 | Samsung Galaxy Tab S9      |  699.99 | Samsung Galaxy Tab S9
           5 | Apple AirPods Pro 3        |  249.99 | Samsung Galaxy Buds Pro 2
           5 | Samsung Galaxy Buds Pro 2  |  199.99 | Samsung Galaxy Buds Pro 2
           6 | Apple Watch Series 9       |  399.99 | Samsung Galaxy Watch 6
           6 | Samsung Galaxy Watch 6     |  349.99 | Samsung Galaxy Watch 6
           8 | Samsung QN900C Neo QLED    | 2999.99 | LG OLED TV C3
           8 | Sony Bravia XR A95K        | 2499.99 | LG OLED TV C3
           8 | LG G3 OLED                 | 2499.99 | LG OLED TV C3
           8 | LG OLED TV C3              | 1999.99 | LG OLED TV C3
           9 | Sony HT-A7000 Soundbar     | 1299.99 | Bose SoundLink Max
           9 | Bose SoundLink Max         |  399.99 | Bose SoundLink Max
          11 | Lenovo ThinkPad X1 Carbon  | 1599.99 | Microsoft Surface Laptop 5
          11 | Dell XPS 15                | 1499.99 | Microsoft Surface Laptop 5
          11 | HP Spectre x360            | 1399.99 | Microsoft Surface Laptop 5
          11 | Microsoft Surface Laptop 5 | 1299.99 | Microsoft Surface Laptop 5
          12 | Apple iMac 24"             | 1299.99 | Dell Inspiron 27
          12 | Dell Inspiron 27           |  999.99 | Dell Inspiron 27Code 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 LAST_VALUE function retrieves the name of the last product in each category based on the highest price.
The RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING defines the frame, which starts at the partition’s first row and ends at the last row.

Summary #

Use the LAST_VALUE function to get the last value in an ordered set of values.

Quiz #

Was this tutorial helpful ?