How to update group no incremental by 1 based on every group from 1 to

Question

I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3 meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc .. create table #Replacement ( PartIDC INT , PartIDX INT , FlagStatus nvarchar(50), HasReplacement nvarchar(50), groupId int , step tinyint ) insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step) values (1222, 3421, 'Invalid' , 'Critical' , NULL , 1), (3421, 6421, 'Valid' , 'Active' , NULL , 2), (1222, 6421, 'Valid' , 'Chain' , NULL , 3), (5643, 2243, 'Invalid' , 'Critical' , NULL , 1), (2243, 3491, 'Valid' , 'Active' , NULL , 2), (5643, 3491, 'Valid' , 'Chain' , NULL , 3) select * from #Replacement Expected result PartIDC PartIDX FlagStatus HasReplacement GroupNo Steps 1222 3421 Invalid Critical 1 1 3421 6421 Valid Active 1 2 1222 6421 Valid Chain 1 3 5643 2243 Invalid Critical 2 1 2243 3491 Valid Active 2 2 5643 3491 Valid Chain 2 3 5643 3491 Valid Chain 2 3 always step 1 and step 3 are both equal on PartIDC always step 1 and step 2 are both equal on PartIDx from step 1 equal to PartIDC from step 2 . so How to do Expected result above by update statement to groupId ?

Shahriar Nikkhah · Answer

--select * from #Replacement ; WITH CTE AS ( SELECT MT.* , PartIDCNew = ST.PartIDC , NewPART = CASE WHEN ST.PartIDC IS NULL THEN MT.PartIDC ELSE ST.PartIDC END FROM ( SELECT * --PartIDC --, PartIDC_COUNT = COUNT(*) FROM #Replacement --GROUP BY PartIDC ) MT LEFT JOIN #Replacement ST ON MT.PartIDC = ST.PartIDX --ORDER BY 1,3 ) SELECT * , NewGROUP = DENSE_RANK() OVER ( ORDER BY NEWPart) FROM CTE

Pankaj Patel · Answer

Hi Ahmed Elbarbary, After enough tries, I have done with following way: create table #Replacement_tmp (ID int identity, PartIDC INT , PartIDX INT ) insert into #Replacement_tmp (PartIDC, PartIDX) select PartIDC, PartIDX from #Replacement where step = 1 declare @i as int declare @total as int declare @PartIDC_ as int declare @PartIDX_ as int set @i = 1 select @total = count (Id) from #Replacement_tmp while @i begin select @PartIDC_ = PartIDC, @PartIDX_ = PartIDX from #Replacement_tmp where Id = @i update #Replacement set groupId = @i where PartIDC = @PartIDC_ update #Replacement set groupId = @i where PartIDC = @PartIDX_ set @i = @i + 1 end drop table #Replacement_tmp Hope, this will help you!

Shahriar Nikkhah · Answer

In general (99.99999% ) of the times and dealling with todays Big Data, I totally disagree with LOOPS in T-SQL scripts with SQL server