New answers tagged

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 ...


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 #...


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, ...


4

As CTE's are for the first test quite difficult to understand, i wrote a approach, with all Levels at the end The Option at the end is nit needed, but to reduce espeically when you are trying to figure out what happens, you shoukld add it at a low number WITH MyTest as ( SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P....


Top 50 recent answers are included