Last_value window function doesn't work properly

Question

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?

Answer 1

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:

1. Oracle takes all rows matching current row's customer id
2. It orders them in an ascending order by valid_from
3. It forms a window starting with minimum valid_from date, and ending with current row's valid_from.
4. 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

Answer 2

first_value and last_value are a bit special in that they require a window on which to operate.

You need to add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING as follows:

select DISTINCT LAST_VALUE(VALID_FROM) OVER (partition by customer_id
  ORDER BY VALID_FROM ASC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) rn
from examp1;

See the documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#i81407 and read especially the section on windowing.

Note that the default clause for functions which accept the windowing clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which explains why one order works while the other doesn't! The default clause is set this way to make it easy to do running-total type calculations without having to specify the window.