PostgreSQL offers several built-in functions known as window functions that allow us to do calculations across the current row. These functions are used to perform various operations, such as calculating cumulative sums, assigning row rankings within partitions, accessing rows before or after the current row, and so on.

This write demonstrates how to use LAST_VALUE Function in Postgres using the following outlines:

- Applying LAST_VALUE() Function Over a Particular Table

- Applying LAST_VALUE() Function Over Table Partitions

How to Use the LAST_VALUE() Function in PostgreSQL?

PostgreSQL offers several built-in window functions with different functionalities. LAST_VALUE() is one of them that retrieves the last value in the sorted partition/window of a result set.

LAST_VALUE (exp)
OVER (
[PARTITION BY partition_col_list]
[ORDER BY order_col_list]
);

Here,

- “exp” represents an expression that can be any valid column, expression, or subquery. It must retrieve a single value.

- “[PARTITION BY partition_col_list]” is an optional clause that splits the result set into partitions based on columns specified in “partition_col_list”. However, if you don't use it, the stated function will consider the entire result set as one partition.

Sample Table

Let’s fetch the data of a sample table named “student_details” using the following statement:

SELECT * FROM student_details;

Example 1: Applying LAST_VALUE() Function Over Entire Table

In this example, we will apply the LAST_VALUE() function on the “student_details” table:

SELECT st_id, st_name, st_gender, st_grade,
LAST_VALUE(st_name) 
OVER(
ORDER BY st_grade
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) FROM student_details;

In the above code snippet:

- The PARTITION BY Clause is skipped, so the LAST_VALUE() function will consider the entire table as one partition.

- Each partition will be sorted in default (ascending) order based on the “st_grade” column.

- The LAST_VALUE() function will pick the last value from the “st_name” column.

- The RANGE BETWEEN clause is used to define the window frame related to the current row in every single partition:

The output demonstrates that LAST_VALUE() selects a value from the last row of the sorted table.

Example 2: Applying LAST_VALUE() Function Over Table Partitions

In this example, we will apply the LAST_VALUE() function on the table’s partition instead of the entire result set:

SELECT st_id, st_name, st_gender, st_grade,
LAST_VALUE(st_name) 
OVER(
PARTITION BY st_gender 
ORDER BY st_grade
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) 
FROM student_details;

In the above code snippet:

- Partitions are created based on the “st_gender” column.

- Each partition will be sorted based on the “st_grade” column.

- The LAST_VALUE() function will pick the last value from the “st_name” column while staying within its associated partition:

The output demonstrates that the LAST_VALUE() function selects a value from the last row based on its partitions.

Conclusion

LAST_VALUE() is one of the Window functions in Postgres that retrieves the last value in the sorted partition/window of a result set. The LAST_VALUE() function picks the last value from the selected column while staying within its associated partition. If the PARTITION BY Clause is skipped/ignored, the LAST_VALUE() function will consider the entire table as one partition. This write-up has demonstrated various use cases of the LAST_VALUE() function.