CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
Steve
Steve

Reputation: 5073

FIRST_VALUE function not working as expected

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?

Upvotes: 1

Views: 1442

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

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.

Upvotes: 0

MT0
MT0

Reputation: 167972

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

Upvotes: 2

haki
haki

Reputation: 9759

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

Upvotes: 0

Related Questions