Home/Learn/SQL/FIRST_VALUE & LAST_VALUE

FIRST_VALUE & LAST_VALUE

Intermediate

Window Functions

FIRST_VALUE returns the first value in the window frame; LAST_VALUE requires an explicit extended frame clause or it will only ever return the current row's own value.

Overview

FIRST_VALUE(expr) and LAST_VALUE(expr) are window functions that return the first or last value in the current frame. FIRST_VALUE is intuitive — the default frame (cumulative from the start of the partition) makes it behave as expected most of the time. LAST_VALUE is a common trap: its default frame ends at the current row, not the end of the partition, so it typically returns the current row's own value rather than the partition's last value. Fixing it requires ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. NTH_VALUE(expr, n) generalises both, returning the nth row's value within the frame.

FIRST_VALUE — Each Employee vs Department Top Earner

FIRST_VALUE with ORDER BY salary DESC and the default cumulative frame returns the highest salary seen so far in the partition — which equals the department maximum because the first row in the ordered partition is always the highest earner.

SQL — FIRST_VALUE for department highest earner

-- Each employee's salary alongside their department's top earner salary
SELECT
    e.name,
    d.name                            AS department,
    e.salary,
    FIRST_VALUE(e.salary) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary DESC
        -- default frame: RANGE UNBOUNDED PRECEDING TO CURRENT ROW
        -- first row in this ordered partition = highest salary
    )                                 AS dept_top_salary,
    FIRST_VALUE(e.name) OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary DESC
    )                                 AS top_earner_name
FROM employees e
JOIN departments d ON d.id = e.department_id;

LAST_VALUE — The Frame Trap and the Fix

The most common LAST_VALUE mistake: forgetting to extend the frame to UNBOUNDED FOLLOWING. Without it, the "last" value is always the current row itself, because the default frame only covers rows up to the current one.

SQL — LAST_VALUE wrong vs correct frame

-- WRONG: default frame makes LAST_VALUE return current row's salary
SELECT
    name,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        -- default: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        -- "last" in this frame = current row = always current salary
    ) AS wrong_lowest_salary
FROM employees;

-- CORRECT: extend frame to end of partition
SELECT
    name,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_lowest_salary    -- now truly the last (lowest) salary in dept
FROM employees;

NTH_VALUE

NTH_VALUE(expr, n) returns the value from the nth row in the frame. Like LAST_VALUE it requires an extended frame when you want the nth value from the end or beyond the current position.

SQL — NTH_VALUE and DENSE_RANK alternative

-- Second-highest salary per department (NTH_VALUE)
SELECT DISTINCT
    department_id,
    NTH_VALUE(salary, 2) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_salary
FROM employees;

-- Alternative using DENSE_RANK (often clearer for top-N)
WITH ranked AS (
    SELECT department_id, salary,
           DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
    FROM employees
)
SELECT DISTINCT department_id, salary AS second_highest_salary
FROM ranked WHERE dr = 2;

Key Points to Remember

1FIRST_VALUE returns the first value in the window frame; with ORDER BY and the default frame it effectively returns the partition-level maximum (or minimum, depending on order direction).
2LAST_VALUE's default frame ends at the current row, not the end of the partition — always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you want the true last value.
3NTH_VALUE(expr, n) generalises FIRST_VALUE (n=1) and LAST_VALUE; it also needs the extended frame for rows beyond the current position.
4Prefer FIRST_VALUE over MIN() window when you need the associated row's other column values, not just the minimum scalar.
5DISTINCT in the outer query removes duplicate partition-level values when only the per-partition result is needed.

Interview Questions

Why does LAST_VALUE often return the current row's own value and how do you fix it?

HardGoogle

Write a query showing each employee's salary and the highest salary in their department.

MediumMicrosoft

What is the difference between FIRST_VALUE and MIN() as window functions?

MediumAdobe

How would you retrieve the second-highest salary per department without using LIMIT?

HardAmazon

Ask Aria about FIRST_VALUE & LAST_VALUE

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

LEAD & LAG

NTILE