CODEWITHSUNDEEP
sqlgroup-bywindow-functionslag
Markus
Markus

Reputation: 1211

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

Upvotes: -1

Views: 869

Answers (1)

DannySlor
DannySlor

Reputation: 4620

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

Upvotes: 2

Related Questions