WindowSQL standard

LAST_VALUE

Takes the value from the last row of the window — but only with the right frame.

Syntax

LAST_VALUE(<expression>) OVER (
  ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

LAST_VALUE(<expression>) OVER (
  PARTITION BY <group> ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Adds to every row the value from the last row of its window. The key detail is the frame: by default the window ends at the current row, so without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING the “last” row is the current row itself. Its mirror function is FIRST_VALUE.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The lowest salary next to each one

SELECT name, salary,

LAST_VALUE(salary) OVER (

ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS lowest

FROM employees;

name	salary	lowest
Olivia	1350	950
Emma	1200	950
Sophia	1200	950
Liam	1000	950
Noah	950	950

With and without a frameAdvanced

SELECT name, salary,

LAST_VALUE(salary) OVER (ORDER BY salary DESC) AS without_frame,

LAST_VALUE(salary) OVER (

ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS lowest

FROM employees;

name	salary	without_frame	lowest
Olivia	1350	1350	950
Emma	1200	1200	950
Sophia	1200	1200	950
Liam	1000	1000	950
Noah	950	950	950

Without a frame the window ends at the current row, so without_frame just repeats the salary. With the full frame, LAST_VALUE sees all the rows and returns the lowest — 950.

Who earns the least in each department

SELECT name, department, salary,

LAST_VALUE(name) OVER (

PARTITION BY department ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS lowest_paid

FROM employees;

name	department	salary	lowest_paid
Emma	backend	1200	Liam
Liam	backend	1000	Liam
Sophia	design	1200	Sophia
Olivia	frontend	1350	Noah
Noah	frontend	950	Noah

Watch out

Without a frame, LAST_VALUE almost always returns the current value: the window ends at the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

It's often simpler to do without a frame: FIRST_VALUE(salary) OVER (ORDER BY salary) gives the same as LAST_VALUE with the full frame and DESC order.

You can't use the result in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

FIRST_VALUE NTH_VALUE LEAD

Learn more in the course

FIRST_VALUE and LAST_VALUE