Last_value over partition by

I have a table that contains column Fruit,Date,Price,Flag.

I use First_value(Price) over (partition by Fruit order by Fruit, Date) to generate Column Price First

last_value(Price) over (partition by Fruit order by Fruit,Date rows between unbounded preceding and unbounded following) to generate Column Price Last

Fruit   Date       Price    Flag    Price first   Price Last
Apple   6/1/2015    1.4     Yes       1.4           1.9
Apple   6/2/2015    1.5     Yes       1.4           1.9
Apple   6/3/2015    2.5     Yes       1.4           1.9
Apple   6/4/2015    3       Yes       1.4           1.9
Apple   6/5/2015    1.7     Yes       1.4           1.9 
Apple   6/6/2015    1.9     No        1.4           1.9

But I need another column "Price Last Flag Yes" to give me the last updated price for fruit Apple that don't have flag No,In this case, it should be the price at 6/5/2015

Fruit   Date       Price    Flag    Price first  Price Last  Price Last Flag Yes
Apple   6/1/2015    1.4     Yes       1.4           1.9        1.7
Apple   6/2/2015    1.5     Yes       1.4           1.9        1.7
Apple   6/3/2015    2.5     Yes       1.4           1.9        1.7
Apple   6/4/2015    3       Yes       1.4           1.9        1.7
Apple   6/5/2015    1.7     Yes       1.4           1.9        1.7 
Apple   6/6/2015    1.9     No        1.4           1.9        1.7

I don't know how to query the condition that filters out the Flag "no" within the functions. I don't want to go through subqueries. It is possible to add conditions and filters within the function? Thanks

I don't want to use where clause because then I need to use subquery. I also tried last_value(case when Flag = 'Yes' then Price end) over (partition by fruit order by Fruit,date), it doesn't work either. For some reason it messes the data and some rows are empty.

Answers


There's the IGNORE NULLS option:

last_value(case when Flag = 'Yes' then Price end ignore nulls) 
over (partition by Fruit order by Date ...

Btw, partition by Fruit, Date order by Fruit,Date will not work, it's partition by Fruit order by Date instead


Need Your Help

Key-value store for Node.js that works under Windows/Node 0.8

node.js leveldb

I am working on a project that needs a fast, embedded and persistent key-value store (like LevelDB), but the development environment is restricted to Windows 7 / Node 0.8. My natural response was t...

How to dispose the context variable in c#?

c# asp.net-mvc entity-framework dispose using-statement

I want to dispose the devDB context object in the below code.