How to create recordset w/ columns for previous 3 column values?

August 10, 2022

I have a table, "Results" w/ the following columns: MatchDate, PlayerID and Score

SELECT * FROM Results WHERE PlayerID = 2 ORDER BY MatchDate DESC

MatchDate	PlayerID	Score
2021-12-01	2	3
2021-11-25	2	2
2021-11-21	2	0
2021-11-20	2	1
2021-10-05	2	2

What I’m trying to do is to build a query that creates the following resultset:

MatchDate	PlayerID	Score	PreviousScore_1	PreviousScore_2	PreviousScore_3
2021-12-01	2	3	2	0	1
2021-11-25	2	2	0	1	2
2021-11-21	2	0	1	2	null
2021-11-20	2	1	2	null	null
2021-10-05	2	2	null	null	null

The above resultset contains the Score for the Player for the current data, as well as the latest 3 previous scores. PreviousScore_1 = the Score for the previous MatchDate for the current player. PreviousScore_2 = the Score for the second previous MatchDate for the current player, a.s.o.

I can’t get my head around this. Is this even possible to achieve in a single query?

>Solution :

You may use LEAD function as the following:

Select MatchDate, PlayerID, Score,
       LEAD(Score) Over (Partition By PlayerID Order By MatchDate DESC) AS PreviousScore_1,
       LEAD(Score, 2) Over (Partition By PlayerID Order By MatchDate DESC) AS PreviousScore_2,
       LEAD(Score, 3) Over (Partition By PlayerID Order By MatchDate DESC) AS PreviousScore_3
From Results

See a demo from db<>fiddle.