Search code examples
sqlgroup-bywindow-functionslag

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

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
Solution 
  • 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