Search code examples
sqloracle-databaselaglead

LAG and the first NULL

I have the below table:

id    date       cust
1     3/13/2019  
1     3/14/2019  Johnson
1     3/19/2019

I want to create a column that captures the last cust entry and partition by id.

I have the below..

select *
,case
 when a.cust is not null then a.cust
 else lag(a.cust) over partition by a.id order by a.date)
 end lst_cust
from A

Results:

id  date        cust
 1   3/13/2019
 1   3/14/2019  Johnson
 1   3/19/2019  Johnson

How do I capture "Johnson" for row one?

I was thinking about using lead too but not sure how to embed both in the case expression and if that's what I'm looking for. Or LAST_VALUE with nulls first but can't see to get it to work.

Solution

  • last_value is a good idea, just add window clause:

    select id, date_, 
       nvl(cust, last_value(cust) ignore nulls over (partition by id order by date_ 
                 rows between unbounded preceding and unbounded following)) cust
  from a
  order by id, date_

    demo