How to make row number start by 1 instead of 0 when make union all?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  October 20, 2021 at 8:11 am

  #3942273

  I work on SQL server 2012 i face issue i can't arrange rows from 1,2,3,4,5

  current arrange is 0,1,2,3 for rownumber

  i need row number start by 1 then 2,3,4,5,etc

  select H.lifecycleid,H.ZPartID,H.ZLC,H.ProccessingDate,ROW_NUMBER() OVER(Partition by H.ZPartID ORDER BY H.ProccessingDate DESC) AS Row_Number into #arrangeHistory
   from #TempLC t inner join parts.LifeCycleHistory H on H.ZPartID=t.ZPartID
      
      --3---get data related to part master and history ordered by row number 
      --master is first and history second
      --master is 0
      --history is bigger than 0
   select * into #tempFullRows from 
   (
                   select m.lifecycleid, m.ZPartID,m.ZLC,m.ProccessingDate,0 as Row_Number
   from  parts.LifeCycleMaster m inner join #TempLC t  on m.ZPartID=t.ZPartID and t.status is null
   union all
                   SELECT * FROM #arrangeHistory
                   )as tempdata

• Steve Collins

  SSCrazy

  Points: 2157

  More actions

  October 20, 2021 at 1:38 pm

  #3942420

  For the counting to begin at 1 maybe you could assign the value 1 to rows where the status is null.  Then UNION ALL for the additional rows with the ROW_NUMBER function +1.  No sample data so maybe this works

  select m.lifecycleid, m.ZPartID, m.ZLC, m.ProccessingDate, 
         1 as row_num
  into #arrangeHistory
  from parts.LifeCycleMaster m
              join #TempLC t on m.ZPartID=t.ZPartID
                                and t.status is null
  union all
  select H.lifecycleid,H.ZPartID,H.ZLC,H.ProccessingDate,
         row_number() over(partition by H.ZPartID order by h.ProccessingDate desc)+1 as row_num
  from #TempLC t
       join parts.LifeCycleHistory H on H.ZPartID=t.ZPartID;
      
  select * 
  from #arrangeHistory
  order by ZPartID, row_num;

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