How to find previous non -1 value

Database

How to find previous non -1 value

Hello Everyone,

I have a scenario like below.

 

Key1  Value  date (unique for row)

123      1

123      4

123      -1

123      2

123      -1

123      -1

123       2

 

The requirement is whenever there is -1 then we need to take previous value. if we have -1 two times then we need to take its previous non -1 value. We can acheive this using rows 1 preceeding 1 preceeding but problem comes when we have multiple -1 values.

 

Could you please  help me.

 

Thanks

Regards,

Kumar

 


Accepted Solutions
Junior Contributor

Re: How to find previous non -1 value

last_value(nullif(value, -1) ignore nulls)
over (partition by key1
      order by date)
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: How to find previous non -1 value

last_value(nullif(value, -1) ignore nulls)
over (partition by key1
      order by date)

Re: How to find previous non -1 value

Dnoeth Thanks a lot. It worked :)