T-SQL LAG function for returning previous rows with different WHERE condition-CodePudding

I have data like:

table name: "Data"

ID  Name   Color  Value
1   A    Blue   1 
2   B    Red    2
3   A    Blue   3 
4   B    Red    4
5   B    Blue   3 
6   A    Red    4

Can I use a SQL LAG function to get for each Name that is Red, the previous value for for that name that was Blue (ordering by ID)?

Result set:

ID Name   Color   Value  PreviousValue

2  B    Red    2   NULL
4  B    Red    4   NULL
6  A    Red    4   3

CodePudding user response：

select   *
from
(
select   *
        ,case when color = 'red' and color != lag(color) over(partition by name order by id) then lag(value) over(partition by name order by ID) end PreviousValue
from     t
) t
where    color = 'red'
order by id

ID	Name	Color	Value	PreviousValue
2	B	Red	2	null
4	B	Red	4	null
6	A	Red	4	3

Fiddle