# Recursive CTE with partition

I have a table like this in MS SQL SERVER 2014:

``````ID|Race|Lap
1 |21  |11
2 |21  |NULL
3 |21  |NULL
4 |21  |NULL
5 |29  |65
6 |29  |NULL
7 |29  |NULL
8 |29  |NULL
``````

I am trying to fill up the Lap column by adding 1 to it based on the first value. The partition is based on Race column. Something like this would be the end result:

``````ID|Race|Lap
1 |21  |11
2 |21  |12
3 |21  |13
4 |21  |14
5 |29  |65
6 |29  |66
7 |29  |67
8 |29  |68
``````

There might be other ways of doing this but I would rather stick with recursive CTE. Is there any way to do this?

• How would a recursive CTE help here? Sounds like an X-Y problem to me. Nov 18 '20 at 18:11
• @mustaccio well the X seems to be too complicated but here is a simpler version I asked which did not get any answer so this is a further simplification. Nov 18 '20 at 18:16

This would produce the expected result:

``````create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);

with CTE as
(select race, ROW_NUMBER() over (partition by race  order by race) "extra_lap" from #demo where lap is null),
CTE2 as
(select race, lap "lap" from #demo where lap is not null)
select race, lap from CTE2
union
select CTE.race, CTE2.lap + CTE.extra_lap "lap" from CTE join CTE2 on CTE.race=CTE2.race

drop table #demo;
``````
• Thanks for the help that worked! Nov 18 '20 at 21:40

also another way using window functions :

``````create table #demo (id int, race int, lap int)
insert into #demo values (1,21,11),(2,21,null),(3,21,null),(4,21,null),(5,29,65),(6,29,null),(7,29,null),(8,29,null);

SELECT * , IIF(lap IS NULL , FIRST_VALUE(lap) OVER (PARTITION BY s.race ORDER BY id ) + RANK() OVER ( PARTITION BY s.race ORDER BY id ) -1 , lap)
FROM #demo AS s
``````
• that works and is much simpler! I hadnt known about FIRST_VALUE, thanks for that! Nov 19 '20 at 13:18

For this kind of cases Recursive CTE can be used as below with no need to partition.

``````--This temp table created to store sample values
SELECT Id,Race,Lap
INTO #LapRecords
FROM (VALUES (1,21,11),(2,21,NULL),(3,21,NULL),(4,21,NULL),(5,29,65),(6,29,NULL),(7,29,NULL),(8,29,NULL)) LapRecords (Id,Race,Lap);

--Recursive CTE
WITH LapRecordsCTE (Id,Race,Lap) AS
(
SELECT Id,Race,Lap FROM #LapRecords WHERE Lap IS NOT NULL
UNION ALL
SELECT LapRecords.Id,LapRecords.Race,LapRecordsCTE.Lap+1
FROM #LapRecords AS LapRecords JOIN LapRecordsCTE
ON LapRecords.Id=LapRecordsCTE.Id+1 AND LapRecords.Race=LapRecordsCTE.Race
)

SELECT Id,Race,Lap FROM LapRecordsCTE ORDER BY Race,Lap;

--Temp table droped to clean up workspace
Drop Table #LapRecords
``````
• that has a flaw , if you remove a row in the middle of group , It will fail to returns the rest ( for example id =7 or id =2 ) , you can't trust id Nov 23 '20 at 20:13