intermediate

LAST_VALUE

By DataPlayArena Technical Review BoardUpdated 2026-07-236 min readRuntime Verified

Overview

LAST_VALUE() returns the last value in a window frame, requiring explicit frame boundary specification to work correctly.

Learning Objectives

  • Retrieve ending boundary values using LAST_VALUE().
  • Understand the default window frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) trap.
  • Expand window frames to UNBOUNDED FOLLOWING.

Detailed Concept Explanation

By default, window functions use a frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under this default frame, LAST_VALUE() simply returns the current row's value because the frame ends at the current row!

To get the actual last value of the entire partition, you must explicitly expand the window frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING


Code Examples

SQL

sql
-- Correct usage of LAST_VALUE with explicit window frame
SELECT 
    name,
    department_id,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_dept_salary
FROM employees;

Interview Perspective

Danger

Classic Bug: LAST_VALUE Returning Current Row

Why does LAST_VALUE(val) OVER (ORDER BY date) return the current row's value?

Because the default frame is CURRENT ROW. Fix it by specifying: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.


Summary

LAST_VALUE() requires an explicit UNBOUNDED FOLLOWING window frame to scan all the way to the end of the partition.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.