Last_value window function doesn't work properly

oracle, window-functions

Solution

In analytic functions you need to specify window range. By default it is `between unbounded preceding and current row`, which I assume to be self-explanatory.

Basically, this is what happens when you specify `partition by customer_id order by valid_from asc`:

- Oracle takes all rows matching current row's `customer id`

- It orders them in an ascending order by `valid_from`

- It forms a window starting with minimum `valid_from` date, and ending with current row's `valid_from`.

- It evaluates `last_value`, which returns your current row's `valid_from`.

What you need to do is specify an ongoing range:

16:53:00 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_38.sql

  1  select last_value(VALID_FROM) OVER (
  2    partition by customer_id
  3    ORDER BY VALID_FROM asc
  4    range between current row and unbounded following
  5  ) rn
  6* from   t
16:53:21 SYSTEM@sandbox> /

RN
---------------------------------------------------------------------------
04-DEC-13 11.07.01.000000 AM
04-DEC-13 11.07.01.000000 AM
04-DEC-13 11.07.01.000000 AM

Elapsed: 00:00:00.01

Problem

`Last_value` window function `doesn't` work properly. ``` CREATE TABLE EXAMP2 ( CUSTOMER_ID NUMBER(38) NOT NULL, VALID_FROM DATE NOT NULL ); Customer_id Valid_from ------------------------------------- 9775 06.04.2013 01:34:16 9775 06.04.2013 20:34:00 9775 12.04.2013 11:07:01 -------------------------------------- select DISTINCT LAST_VALUE(VALID_FROM) OVER (partition by customer_id ORDER BY VALID_FROM ASC) rn from examp1; ``` When I use `LAST_VALUE` then I get following rows: ``` 06.04.2013 20:34:00 06.04.2013 01:34:16 12.04.2013 11:07:01 ``` When I use `FIRST_VALUE` then I get following rows: ``` select DISTINCT FIRST_VALUE(VALID_FROM) OVER (partition by customer_id ORDER BY VALID_FROM DESC) rn from examp1; 4/12/2013 11:07:01 AM ``` `First_value` query gives correct output. I hoped to get same output from these queries. Why do I have `2 different results`?

Original source