CODEWITHSUNDEEP
sqloracle-database
Sailormoon
Sailormoon

Reputation: 267

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.

Upvotes: 4

Views: 2026

Answers (1)

dnoeth
dnoeth

Reputation: 60462

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

Upvotes: 3

Related Questions