LAST_VALUE returns the value from the last row of the window. It is also the analytic function most often used wrongly: with an ORDER BY and the default frame, the window ends at the current row, so LAST_VALUE quietly returns each row's own value. Knowing the frame makes it work as intended.
Code for This Guide
The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
last_value(expr) [{respect | ignore} nulls]
over ([partition by ...] order by ...
rows between unbounded preceding and unbounded following)To get the last row of the whole partition, give the frame explicitly as above. IGNORE NULLS returns the last non-NULL value in the window.
The Default Frame Trap
Example:
-- with ORDER BY and the default frame, LAST_VALUE returns the current row
select last_name, hire_date,
last_value(last_name) over (partition by department_id order by hire_date) as default_frame,
last_value(last_name) over (partition by department_id order by hire_date
rows between unbounded preceding and unbounded following)
as whole_partition
from employees
where department_id = 50
order by hire_date;Output:
LAST_NAME HIRE_DATE DEFAULT_FRAME WHOLE_PARTITION ____________ ______________ ________________ __________________ Mehta 01-SEP-2015 Mehta Schmidt Reddy 06-JUN-2016 Reddy Schmidt Williams 18-FEB-2019 Williams Schmidt Schmidt 04-SEP-2023 Schmidt Schmidt
With the default frame, the window of each row runs from the first row to the current one, so its last row is the current row, and default_frame simply repeats the employee's own name. With the frame extended to the whole partition, every row gets Schmidt, the latest hire.
Last Hire with a Named Window
The correct frame can be defined once in a WINDOW clause and shared by several functions.
Example:
select department_id, last_name, hire_date,
first_value(last_name) over w as first_hired,
last_value(last_name) over w as last_hired,
nth_value(last_name, 2) over w as second_hired
from employees
where department_id in (50, 60)
window w as (partition by department_id order by hire_date
rows between unbounded preceding and unbounded following)
order by department_id, hire_date;Output:
DEPARTMENT_ID LAST_NAME HIRE_DATE FIRST_HIRED LAST_HIRED SECOND_HIRED
________________ ____________ ______________ ______________ _____________ _______________
50 Mehta 01-SEP-2015 Mehta Schmidt Reddy
50 Reddy 06-JUN-2016 Mehta Schmidt Reddy
50 Williams 18-FEB-2019 Mehta Schmidt Reddy
50 Schmidt 04-SEP-2023 Mehta Schmidt Reddy
60 Okafor 01-FEB-2016 Okafor Mensah Costa
60 Costa 29-JAN-2018 Okafor Mensah Costa
60 Mensah 09-MAY-2022 Okafor Mensah Costa
7 rows selected.Things to Know
- Alternatively, FIRST_VALUE with the order reversed (ORDER BY hire_date DESC) returns the last value with the default frame.
- LAST_VALUE(x IGNORE NULLS) OVER (ORDER BY ...) with the default frame is useful on purpose: it carries the last known value forward to later rows.
- In aggregate queries, MAX(x) KEEP (DENSE_RANK LAST ORDER BY ...) returns the last value once per group.
Related Guides
Conclusion
LAST_VALUE returns the value from the last row of the window. For the last row of the partition, specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or reverse the order and use FIRST_VALUE; with the default frame, it returns the current row.
