LAST_VALUE

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

-- 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

Summary

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