CODEWITHSUNDEEP
sqlsql-server
CandleWax
CandleWax

Reputation: 2219

How to use the LAG in SQL Server within a group?

Below is a list of different people and their occupation before they became zombies. I need to find the occupation just before the person became a zombie. The last individual became a zombie, then reverted back to zoo keeper and finally became a zombie again. In this case, I just need the occupation just before the first zombie transformation.

Recordset:

Person    Occupation    Order_of_events
---------------------------------------
1         Lawyer        1
1         Dog Walker    2
1         Zoo Keeper    3
1         Zombie        4
1         Driver        5
2         Lifeguard     1
2         Zombie        2
3         Zoo Keeper    1
3         Zombie        2
3         Driver        3
3         Zombie        4

Final Result

Person    Occupation
---------------------
1         Zoo Keeper
2         Lifeguard
3         Zoo Keeper

My attempt:

SELECT 
    person, occupation, Order_of_events,
    LAG(occupation, 1, 'Always a zombie') OVER (PARTITION BY person ORDER BY Order_of_events) AS [previous occupation]
FROM 
    table

I think my issue is in the partition but I am getting confused on how to select the previous row where zombie was the occupation.

I'm using SQL Server 2017.

Upvotes: 0

Views: 58

Answers (2)

Vaidøtas I.
Vaidøtas I.

Reputation: 554

I would suggest using LEAD to search for one row in the remaining list for "zombie" and do a group by to eliminate dupes (take the first found occupation for id). This could work without the self-join solution.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You don't actually need lag() for this. But window functions will help:

select top (1) with ties tprev.*
from t join
     t tprev
     on t.person = tprev.person 
    and t.Order_of_events = tprev.Order_of_events + 1
where t.occupation = 'Zombie'
order by row_number() over (partition by t.person order by t.Order_of_events);

Upvotes: 3

Related Questions