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

Question

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

Answer 1

select m.lifecycleid, m.ZPartID,m.ZLC,m.ProccessingDate, 0 as Row_Number

It starts with 0, because you defined it so in your query.

Answer 2

ROW_NUMBER() always starts with 1.

Your field Row_Number is 0 because of this statement

...,0 as Row_Number

Answer 3

As other have said, you have yourself set 0 as row_number....

But then you will get 1 twice, but you can easily address this by adding 1 to the row_number function in the outer SELECT.

select H.lifecycleid,H.ZPartID,H.ZLC,H.ProccessingDate,ROW_NUMBER() OVER(Partition by H.ZPartID ORDER BY H.ProccessingDate DESC) +  1 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,1 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

Answer 4

Hi @ahmed salah ，

Please also check:

 select lifecycleid, ZPartID,ZLC,ProccessingDate,(Row_Number+1) as Row_Number  
 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  

Or:

 select lifecycleid, ZPartID,ZLC,ProccessingDate,  
 ROW_NUMBER() OVER(Partition by ZPartID ORDER BY ProccessingDate DESC) AS Row_Number  
 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  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".