LAST_VLAUE

Description of last_value.eps follows

PURPOSE

LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values.

{RESPECT | IGNORENULLS

{RESPECT | IGNORENULLS determines whether null values of expr are included in or eliminated from the calculation. The default is RESPECT NULLS. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then LAST_VALUE returns the last non-null value in the set, or NULL if all values are null. Refer to “Using Partitioned Outer Joins: Examples” for an example of data densification.

If you omit the windowing_clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default sometimes returns an unexpected value, because the last value in the window is at the bottom of the window, which is not fixed. It keeps changing as the current row changes. For expected results, specify the windowing_clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Alternatively, you can specify the windowing_clause as RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

EXAMPLE 1:

SELECT employee_id, last_name, salary, hire_date,
LAST_VALUE(hire_date)
OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) AS lv
FROM (SELECT * FROM employees
WHERE department_id = 90
ORDER BY hire_date);

EMPLOYEE_ID LAST_NAME SALARY HIRE_DATE LV


https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LAST_VALUE.html

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top