0

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. – mustaccio Nov 18 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. – SQLserving Nov 18 at 18:16
2

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;
| improve this answer | |
  • Thanks for the help that worked! – SQLserving Nov 18 at 21:40
2

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
| improve this answer | |
  • that works and is much simpler! I hadnt known about FIRST_VALUE, thanks for that! – SQLserving Nov 19 at 13:18
0

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
| improve this answer | |
  • 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 – eshirvana Nov 23 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.