SQL FIRST_VALUE() and LAST_VALUE()

HR wants every employee's row to also show their department's top earner's name, for quick comparison. FIRST_VALUE() does that correctly on the first try. Asking for the department's lowest earner the same way, with LAST_VALUE(), is where almost everyone gets a confusing, wrong-looking result the first time.

Learning Objectives

• Define FIRST_VALUE() and LAST_VALUE(), and what "first" and "last" actually mean, relative to the frame, not the partition.
• Reproduce, and fix, LAST_VALUE()'s classic default-frame mistake.
• Write a window frame explicitly, using ROWS BETWEEN.

What FIRST_VALUE() and LAST_VALUE() Actually Return

Both return a value from a specific row relative to the current row's window frame, the actual range of rows the function considers, not necessarily the entire partition. FIRST_VALUE() returns the frame's first row's value, LAST_VALUE() returns the frame's last row's value. The distinction between "frame" and "partition" is exactly where LAST_VALUE() goes wrong by default.

FIRST_VALUE() Works as Expected

This correctly shows the department's top earner's name on every row. It works because the default frame, when ORDER BY is present, runs from the partition's start up through the current row, and the current row is always at or after the frame's start, so FIRST_VALUE() always reaches that true first row correctly.

SELECT first_name, department_id, salary,
       FIRST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS top_earner
FROM employees;

LAST_VALUE() Doesn't, By Default

This does not return the department's lowest earner. The default frame (UNBOUNDED PRECEDING to CURRENT ROW, whenever ORDER BY is present) means the frame's "last row" is always the current row itself, so lowest_earner just echoes each row's own first_name right back, not the actual lowest-paid person in the department.

SELECT first_name, department_id, salary,
       LAST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS lowest_earner
FROM employees;

The Fix: an Explicit Frame

SELECT first_name, department_id, salary,
       LAST_VALUE(first_name) OVER (
         PARTITION BY department_id ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_earner
FROM employees;

Try It Yourself

Fix this query so it correctly shows the most recently hired employee's name in every row of each department, on PostgreSQL: SELECT first_name, department_id, hire_date, LAST_VALUE(first_name) OVER (PARTITION BY department_id ORDER BY hire_date) AS most_recent_hire FROM employees;

SELECT first_name, department_id, hire_date,
       LAST_VALUE(first_name) OVER (
         PARTITION BY department_id ORDER BY hire_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS most_recent_hire
FROM employees;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Always write an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame alongside LAST_VALUE() whenever ORDER BY is present.
• Test FIRST_VALUE()/LAST_VALUE() results against a row you can verify by eye before trusting them in a larger query.
• When in doubt about frame behavior, an explicit frame is always safer than relying on the default.

Interview Questions

Summary

FIRST_VALUE() and LAST_VALUE() both read from the window frame, not necessarily the whole partition, a distinction that only matters once ORDER BY is involved. FIRST_VALUE() works correctly by default because the frame's start is always the true first row. LAST_VALUE() does not, its default frame ends at the current row, not the partition's true last row, requiring an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to fix.

What's Next?

SQL Running Totals and Moving Averages is next: SUM() and AVG() as window functions, where, unlike LAST_VALUE(), the default frame is actually exactly what's needed.