Intermediate LevelSQL & MySQL
How do FIRST_VALUE() and LAST_VALUE() extract boundary values in window frames?
❓ Interview Prompt: Explain `FIRST_VALUE()` and `LAST_VALUE()` window functions, default window frames (`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`), and explicit frame specification.
The 'FIRST_VALUE()' and 'LAST_VALUE()' window functions return the first or last value in an ordered window frame partition:
⚠️ Frame Trap for 'LAST_VALUE()':
By default, the window frame is 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'. Because the default frame ends at the current row, 'LAST_VALUE()' will return the current row's value instead of the partition's true last value!
•
Fix: Explicitly specify 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'.
Code Implementation Example
sql
SELECT
department_id,
employee_name,
salary,
LAST_VALUE(employee_name) OVER (
PARTITION BY department_id
ORDER BY salary ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS highest_paid_employee
FROM employees;💡Extracting the highest-paid employee per department using LAST_VALUE and explicit window framing.
🎯 Interview Key Takeaway
Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using LAST_VALUE.
#SQL#FIRST_VALUE#LAST_VALUE#Window Frames#Intermediate
Back to all SQL & MySQL Questions→