sql - Window Functions: last_value(ORDER BY ... ASC) same as last_value(ORDER BY ... DESC)

Question

Sample data

CREATE TABLE test
    (id integer, session_ID integer, value integer)
;

INSERT INTO test
    (id, session_ID, value)
VALUES
    (0, 2, 100),
    (1, 2, 120),
    (2, 2, 140),
    (3, 1, 900),
    (4, 1, 800),
    (5, 1, 500)
;

Current query

select 
id,
last_value(value) over (partition by session_ID order by id) as last_value_window,
last_value(value) over (partition by session_ID order by id desc) as last_value_window_desc
from test
ORDER BY id

I was running into a problem with the last_value() window function: http://sqlfiddle.com/#!15/bcec0/2

In the fiddle I am trying to work with the sort direction within the last_value() query.

Edit: The question is not: Why I don't get the all time last value and how to use the frame clause (unbounded preceding and unbounded following). I know about the difference of first_value(desc) and last_value() and the problem that last_value() does not give you the all-time last value:

The default frame clause is unbounded preceding until current row. So first value is always giving the first row withing the clause. So it doesn't matter if there is just one row (the frame clause includes only this one) or one hundered (the frame clause includes all hundred). The result is always the first one. In DESC order it is the same: DESC changes the sort order and then the first row is the last value, no matter how many rows you get.

With last_value() the behavior is very similar: If you have one row, it gives you the last value of the default frame clause: This one row. At the second row, the frame clause contains the two rows, the last one is the second. That's why last_value() does not give you the last row of all rows but only the last row until the current row.

But if I change the order to DESC I am expecting that I have the last row of all first, so I get this one at the first row, than the last but second one at the second row and so on. But that's not the result. Why?

For the current example these are the results for first_value(), first_value(desc), last_value(), last_value(desc) and what I am expecting for the last_value(desc):

 id | fv_asc | fv_desc | lv_asc | lv_desc | lv_desc(expecting)
----+--------+---------+--------+---------+--------------------
  0 |    100 |     140 |    100 |     100 |    140
  1 |    100 |     140 |    120 |     120 |    120
  2 |    100 |     140 |    140 |     140 |    100
  3 |    900 |     500 |    900 |     900 |    500
  4 |    900 |     500 |    800 |     800 |    800
  5 |    900 |     500 |    500 |     500 |    900

For me it seems that the ORDER BY DESC flag is ignored within the default frame clause last_value() call. But it is not within the first_value() call. So my question is: Why is the last_value() result the same as the last_value(desc)?

Answer 1

The problem with LAST_VALUE() is that the default rules for windowing clauses remove the values that you really want. This is a very subtle problem and is true in all databases that support this functionality.

This comes from an Oracle blog:

Whilst we are on the topic of windowing clauses, the implicit and unchangeable window clause for the FIRST and LAST functions is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, in other words all rows in our partition. For FIRST_VALUE and LAST_VALUE the default but changeable windowing clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, in other words we exclude rows after the current one. Dropping rows off the bottom of a list makes no difference when we are looking for the first row in the list (FIRST_VALUE) but it does make a difference when we are looking for the last row in the list (LAST_VALUE) so you will usually need either to specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly when using LAST_VALUE or just use FIRST_VALUE and reverse the sort order.

Hence, just use FIRST_VALUE(). This does what you want:

with test (id, session_ID, value) as (
      (VALUES (0, 2, 100),
              (1, 2, 120),
              (2, 2, 140),
              (3, 1, 900),
              (4, 1, 800),
              (5, 1, 500)
      )
     )
select id,
       first_value(value) over (partition by session_ID order by id) as first_value_window,
       first_value(value) over (partition by session_ID order by id desc) as first_value_window_desc
from test
order by id