Last_value window function not working properly

Question

Last_value window function doesn't work correctly.

 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 , I get the following lines:

 06.04.2013 20:34:00 06.04.2013 01:34:16 12.04.2013 11:07:01 

When I use FIRST_VALUE , I get the following lines:

 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 

Request

FIRST_VALUE gives the correct result. I was hoping to get the same result from these queries. Why do I have 2 different results ?

Answer 1

In analytic functions you need to specify a range of windows. By default, this is between unbounded preceding and current row , which I find understandable.

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

• Oracle takes all rows matching the current row customer id
• It arranges them in ascending order on valid_from
• It forms a window starting with the minimum valid_from date and ending with the current valid_from line.
• It evaluates last_value , which returns your current valid_from string.

What you need to do is specify a constant 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 slightly different in that they require a window to work.

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 especially read the section on the window. Not that the default clause for functions that accept a windowing clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , which explains why one order works and the other doesn't!