Introduction to PostgreSQL LAST_VALUE() function

TheLAST_VALUE() function returns the last value in an ordered partition of a result set.

The syntax of theLAST_VALUE() function is as follows:

LAST_VALUE ( expression )OVER (    [PARTITION BY partition_expression, ... ]    ORDER BY sort_expression [ASC | DESC], ...)

In this syntax:

expression

Theexpression can be an expression, column, orsubquery evaluated against the value of the last row in an ordered partition of the result set.

Theexpression must return a single value. Additionally, it cannot be a window function.

PARTITION BY clause

ThePARTITION BY clause divides rows of the result set into partitions to which theLAST_VALUE() function is applied.

If you omit thePARTITION BY clause, theLAST_VALUE() function will treat the whole result set as a single partition.

ORDER BY clause

TheORDER BY clause specifies the sort order for rows in each partition to which theLAST_VALUE() function is applied.

frame_clause

Theframe_clause defines the subset of rows in the current partition to which theLAST_VALUE() function is applied.

PostgreSQL LAST_VALUE() function examples

We will use theproducts table created in the window function tutorial for the demonstration:

Here are the contents of the data of theproducts table:

1) Using PostgreSQL LAST_VALUE() over a result set example

The following example uses theLAST_VALUE() function to return all products together with the product that has the highest price:

SELECT    product_id,    product_name,    price,    LAST_VALUE(product_name)    OVER(        ORDER BY price        RANGE BETWEEN            UNBOUNDED PRECEDING AND            UNBOUNDED FOLLOWING    ) highest_priceFROM    products;

In this example:

• Since we omit thePARTITION BY clause in theLAST_VALUE() function, the function treats the whole result set as a single partition.
• TheORDER BY clause sorts products by prices from low to high.
• TheLAST_VALUE() retrieves the product name of the last row in the result set.

2) Using PostgreSQL LAST_VALUE() over a partition example

The following example uses theLAST_VALUE() function to return all products together with the most expensive product per product group:

SELECT    product_id,    product_name,    group_id,    price,    LAST_VALUE(product_name)    OVER(PARTITION BY group_id        ORDER BY price        RANGE BETWEEN            UNBOUNDED PRECEDING AND            UNBOUNDED FOLLOWING    ) highest_priceFROM    products;

In this example:

• ThePARTITION BY clause divides rows by group id into three partitions specified by group id 1, 2, and 3.
• TheORDER BY clause sorts products in each product group ( or partition) from low to high.
• TheRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING clause defines the frame starting from the first row and ending at the last row of each partition.
• TheLAST_VALUE() function applies to each partition separately and returns the product name of the last row in each partition.

Summary

• Use the PostgreSQLLAST_VALUE() window function to return the last value in an ordered partition of a result set.

Last updated onFebruary 15, 2024

On this page

• Introduction to PostgreSQL LAST_VALUE() function
• PostgreSQL LAST_VALUE() function examples
• Summary