FIRST_VALUE function not working as expected

Question

I thought I understood how it works but now I am confused.

I have a dataset:

id date     value
1  20080101 null
2  20090101 34
3  20100101 null

Three records, from Jan 2008, 2009 and 2010. Now I want to create a new column "value2" with the latest non-null value. Basically I want a value2 column with 3 34s. I write:

select id, date, value, first_value(value) ignore nulls over (order by date desc) as value2 from table

However, I got:

id date     value   value2
1  20080101 null    34
2  20090101 34      34
3  20100101 null    null

The last value is still null not 34. What is going wrong here?

Answer 1

You are forgetting about the window. Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning your value is being looked up in a window from first to current row. Your sorted rows are:

id date     value
3  20100101 null
2  20090101 34
1  20080101 null

• For id 1 the records 3, 2, 1 are in focus. Value 34 is found in the second row of these.
• For id 2 the records 3, 2 are in focus. Value 34 is found in the second row of these.
• For id 3 only the record 3 is in focus. Hence value 34 cannot be found.

BTW: There is a typo in your request: It is first_value(value ignore nulls), not first_value(value) ignore nulls.

Answer 2

Try eliminating the null valus in the order by clause

A possible solution might be

with x as (
    select 1  as id , 20080101 as ddate , null as v from dual union all 
    select 2 , 20090101 ,34 from dual union all 
    select 3 , 20100101 ,null from dual union all 
    select 4 , 20090101 ,15 from dual union all 
    select 5 , 20110101 ,null from dual union all 
    select 6 , 20120101 ,null from dual union all 
    select 7 , 20030101 ,55 from dual
) 
select  x.* , 
    first_value(v) over (order by case when v is null then null else ddate end) as last_nn_v
from    x
order by ddate
/

        ID      DDATE          V  LAST_NN_V
---------- ---------- ---------- ----------
         7   20030101         55         55
         1   20080101                    55
         4   20090101         15         55
         2   20090101         34         55
         3   20100101                    55
         5   20110101                    55
         6   20120101                    55

Answer 3

The default window for analytic functions is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if you change it to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING then you will get the desired result:

Query 1

WITH table_name AS (
            SELECT 1 AS id, TO_DATE( '20080101', 'YYYYMMDD' ) AS "date", NULL AS value FROM DUAL
  UNION ALL SELECT 2, TO_DATE( '20090101', 'YYYYMMDD' ), 34   FROM DUAL
  UNION ALL SELECT 3, TO_DATE( '20100101', 'YYYYMMDD' ), NULL FROM DUAL
)
SELECT id,
       "date",
       value,
       FIRST_VALUE( value IGNORE NULLS ) OVER (ORDER BY "date" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS value2
FROM   table_name;

Results

ID  | date                   | VALUE   | VALUE2
-------------------------------------------------
3   | 2010-01-01 00:00:00    | (null)  | 34
2   | 2009-01-01 00:00:00    | 34      | 34
1   | 2008-01-01 00:00:00    | (null)  | 34