PostgreSQL – LAST_VALUE Function

ByAdmin

The LAST_VALUE() function is used to retrieve the last value in an ordered set of rows within a result set. Similar to the FIRST_VALUE() function, the LAST_VALUE() function operates within a window of rows defined by the OVER clause, and it’s often used to get the latest or last occurrence of a certain value within a specific order.

The syntax of the LAST_VALUE() function is as follows:

LAST_VALUE(expression) OVER (PARTITION BY partition_expression ORDER BY sort_expression)
  • expression: This is the column or expression whose last value you want to retrieve.
  • PARTITION BY: An optional clause that divides the result set into partitions or groups. The function is applied separately within each partition. If omitted, the function is applied to the entire result set.
  • ORDER BY: Specifies the column(s) by which you want to order the result set for determining the last value.

Here’s a simple example of how to use the LAST_VALUE() function in PostgreSQL:

Suppose you have a table named “sales” with columns “product_name”, “sale_date”, and “revenue”, and you want to find the last sale date for each product:

SELECT product_name, sale_date, revenue, LAST_VALUE(sale_date) OVER (PARTITION BY product_name ORDER BY sale_date) AS last_sale_date FROM sales;

In this example, the LAST_VALUE() function retrieves the last sale date for each product. It orders the rows within each product partition by sale date and extracts the latest sale date. The result might look like this:

product_name | sale_date | revenue | last_sale_date

-------------+------------+---------+----------------

Product A | 2023-01-05 | 100 | 2023-02-10

Product A | 2023-02-10 | 150 | 2023-02-10

Product B | 2023-03-15 | 200 | 2023-03-20

Product B | 2023-03-20 | 180 | 2023-03-20

In this output, the “last_sale_date” column shows the latest sale date for each product based on the order of sale dates.

The LAST_VALUE() function is useful when you want to extract specific information related to the last occurrence of a certain condition or value within a partitioned window of data.

Similar Posts