question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft commented

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
sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Please provide queries to create the relevant table(s) and insert some sample data so we will be able to test your query and provide a solution which we tested.
In addition please provide description of the expected result.

If you can simplify your sample then better. present a simple scenario with the same issue so we will discuss it and not a complex table(s) which are not relevant to the specific issue

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

ROW_NUMBER() always starts with 1.

Your field Row_Number is 0 because of this statement

 ...,0 as Row_Number
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ahmedsalah-1628

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.