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

ahmed salah 3,216 Reputation points
2021-10-20T08:08:08.323+00:00

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
Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 47,441 Reputation points
    2021-10-20T08:16:32.237+00:00

    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.

    0 comments No comments

  2. Tom Phillips 17,771 Reputation points
    2021-10-20T11:20:10.96+00:00

    ROW_NUMBER() always starts with 1.

    Your field Row_Number is 0 because of this statement

    ...,0 as Row_Number
    
    0 comments No comments

  3. Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator
    2021-10-20T21:52:23.013+00:00

    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
    
    0 comments No comments

  4. EchoLiu-MSFT 14,621 Reputation points
    2021-10-21T02:48:19.893+00:00

    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.