Value Boundary Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE
When analyzing sequential data, business users frequently want to compare current values against the extremes or specific milestones of a group:
What was the initial opening price of a stock compared to each trade during the day?
What was the most recent purchase amount recorded before this transaction?
Who was the second highest scoring student in each department?
MySQL 8.0 provides three specialized value boundary functions: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE().
FIRST_VALUE() Mechanics
FIRST_VALUE(expr) returns the value of expr from the first row of the window frame:
1SELECT
2 emp_id,
3 department,
4 first_name,
5 salary,
6-- Compare every employee's salary to the top earner in their department
7 FIRST_VALUE(salary)OVER(
8PARTITIONBY department
9ORDERBY salary DESC
10)AS top_dept_salary,
11ROUND(salary - FIRST_VALUE(salary)OVER(
12PARTITIONBY department
13ORDERBY salary DESC
14),2)AS diff_from_top
15FROM employees;
The LAST_VALUE() Gotcha: The Default Frame Trap!
Many developers encounter an unexpected bug when using LAST_VALUE():
1-- WRONG! Seems to return current row's value rather than the group's last value!
2SELECT
3 department,
4 salary,
5 LAST_VALUE(salary)OVER(
6PARTITIONBY department
7ORDERBY salary ASC
8)AS bad_last_value
9FROM employees;
Why does this happen?
Remember the default window frame with ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under this default, the "last row" in the frame is the current row!
The Fix:
To make LAST_VALUE() evaluate the entire partition, expand the frame:
1-- CORRECT! Explicit frame spanning the entire partition
NTH_VALUE(expr, N) retrieves the value from the N-th row in the ordered window frame. If N exceeds the number of rows evaluated in the frame, it returns NULL.
1-- Find the runner-up (2nd highest) salary in each department