Do we always need to use UNBOUNDED FOLLOWING while using last_value() function?

user_16921198 0
Viewed 88

In SQL, do we always need to use UNBOUNDED FOLLOWING while using last_value function in analytical functions?

I saw this example where if we just used:

SELECT employee_id, salary, last_value(salary) over(order by salary ASC)
FROM employees;

This did not give the expected output which was supposed to give the employee with the highest salary.

Instead they had to use windowing:

SELECT employee_id, salary, 
    last_value(salary) over (
        order by salary ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM employees;

So I was just wondering if while using last_value function, do we always need to use UNBOUNDED FOLLOWING?

oracle sql
1 Answers

The answer is basically "yes", because the default window frame is range between unbounded preceding and current row. And the last value would normally be on the current row.

One exception is when you are using ignore nulls. Then last_value(ignore nulls) is very useful for getting the most recent row -- including the current row -- with a non-NULL value.

By the way, this is the reason that I usually use first_value() with a reversed order by:

first_value(salary) over (order by salary desc)

Or for this example, max() is simpler:

max(salary) over ()
user_11440350
Related