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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

September 13, 2020 at 12:25 am

#3788114

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

PartIDCPartIDXFlagStatusHasReplacementGroupNoSteps
12223421InvalidCritical11
34216421ValidActive12
12226421ValidChain13
56432243InvalidCritical21
22433491ValidActive22
56433491ValidChain23

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 ?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

I don't see any way to group these so that you can use ROW_NUMBER() with a PARTITION clause. Can you explain how these are supposed to group? If you can't group them at all, then you might have to use a cursor. If you can, then this is trivial.

Steve Collins SSCrazy Points: 2167 More actions · Answer 2

;with grp_cte as (
    select distinct r.PartIDC, r.PartIDX, r.step
    from #Replacement r
         join #Replacement r2 on r.PartIDC=r2.PartIDC
                                 and r2.step=3
         join #Replacement r3 on r.PartIDX=r3.PartIDC
                                 and r3.step=2
    where r.step=1)
update r
set groupId=1
from #Replacement r
     join grp_cte gc on r.PartIDC=gc.PartIDC
                        and r.PartIDX=gc.partIDX
                        and r.step=gc.step;

Output

PartIDCPartIDXFlagStatusHasReplacementgroupId        step
12223421Invalid        Critical1        1
34216421Valid        Active        NULL        2
12226421Valid        Chain        NULL        3
56432243Invalid        Critical1        1
22433491Valid        Active        NULL        2
56433491Valid        Chain        NULL        3

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können