SQLTeam.com | Weblogs | Forums

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

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

First you create a table with a row_number, it starts with 1

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

Then you create the number 0 yourself:
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

So if you change the 0 to 1 you create 1,1,2,3,4, etc

If you change the SELECT * FROM #arrangeHistory, you better specify all columns and row_number + 1 you get the results you want.