How to Use last_value Function in PostgreSQL: Syntax and Examples

In PostgreSQL, the last_value function returns the last value in an ordered set of rows within a window frame. It is used with the OVER() clause to define the window and order. To get expected results, you often need to adjust the window frame using ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Syntax

The last_value function syntax in PostgreSQL is:

• last_value(expression) OVER ( [PARTITION BY partition_expression] ORDER BY order_expression [frame_clause] )

Explanation:

• expression: The column or value to get the last value of.
• PARTITION BY: Optional. Divides rows into groups.
• ORDER BY: Defines the order of rows in the window.
• frame_clause: Defines the subset of rows for the window frame (important for correct results).

sql

last_value(expression) OVER (PARTITION BY partition_expression ORDER BY order_expression ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Example

This example shows how to use last_value to get the last salary in each department ordered by employee ID.

sql

CREATE TEMP TABLE employees (
  id SERIAL PRIMARY KEY,
  department TEXT,
  salary INT
);

INSERT INTO employees (department, salary) VALUES
('Sales', 50000),
('Sales', 55000),
('Sales', 60000),
('HR', 45000),
('HR', 47000);

SELECT
  id,
  department,
  salary,
  last_value(salary) OVER (
    PARTITION BY department
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_salary_in_dept
FROM employees
ORDER BY department, id;

Output

 id | department | salary | last_salary_in_dept 
----+------------+--------+---------------------
  1 | Sales      |  50000 |               60000
  2 | Sales      |  55000 |               60000
  3 | Sales      |  60000 |               60000
  4 | HR         |  45000 |               47000
  5 | HR         |  47000 |               47000

Common Pitfalls

A common mistake is to omit the ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame clause. Without it, last_value returns the last value of the current frame, which by default ends at the current row, causing unexpected results.

Wrong usage example:

sql

SELECT
  id,
  salary,
  last_value(salary) OVER (ORDER BY id) AS wrong_last_value
FROM employees
ORDER BY id;

Output

 id | salary | wrong_last_value 
----+--------+------------------
  1 |  50000 |            50000
  2 |  55000 |            55000
  3 |  60000 |            60000
  4 |  45000 |            45000
  5 |  47000 |            47000

Quick Reference

Clause	Description
last_value(expression)	Returns the last value of the expression in the window frame
PARTITION BY	Divides rows into groups for separate calculations
ORDER BY	Defines the order of rows in each partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	Sets the window frame to include all rows in the partition, ensuring last_value returns the true last value

Key Takeaways

Use last_value with an OVER() clause specifying ORDER BY to define row order.

Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the actual last value in the partition.

Without adjusting the frame, last_value returns the last value up to the current row, which may be unexpected.

Partition your data with PARTITION BY to get last values per group.

Test your queries to confirm last_value returns the intended results.