Windows OLAP query PRECEDING ROWS bounded by column value

Database

Windows OLAP query PRECEDING ROWS bounded by column value

Can someone help with writing a query to return most recent previous row in a partition where the row has a column with a certian value but I want to return a different column value than the one which has the criteria.

E.g if data looks like this (dates are YYYY-MM--DD format, so all are in April 2016) I want to return all the data for each row and an additional column which shows the _VALUE for the most recent previous row where the _CODE = A within a partition of _id

data

_id _date _code _value

1 2016-04-01 A 10

1 2016-04-02 B 20

1 2016-04-03 A 30

1 2016-04-04 C 0

2 2016-04-01 B 30

2 2016-04-02 A 30

2 2016-04-03 A 20

2 2016-04-05 C 30

desired result set

_id _date _code _value _most _recent_previous_A_value

1 2016-04-01 A 10 null

1 2016-04-02 B 20 10

1 2016-04-03 A 30 10

1 2016-04-04 C 0 30

2 2016-04-01 B 30 null

2 2016-04-02 A 30 null

2 2016-04-03 A 20 30

2 2016-04-05 C 30 20

I guess something like

partition by _id,

order by _date asc

rows between unbounded preceding and 1 preceding

last_value(_code) ='A'

return _value

Struggling with the syntax, or if it's even possible in a single pass.

1 2016-04-01 A 10

1 2016-04-02 B 20

1 2016-04-03 A 30

1 2016-04-04 C 0

2 2016-04-01 B 30

2 2016-04-02 A 30

2 2016-04-03 A 20

2 2016-04-05 C 30

1 REPLY

Re: Windows OLAP query PRECEDING ROWS bounded by column value

ignore last data snippet - copy / paste error