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

Question

0.00/5 (No votes)

See more:

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 row number
i need row number start by 1 then 2,3,4,5,etc

What I have tried:

SQL

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

Posted 19-Oct-21 22:14pm

ahmed_sa

Updated 19-Oct-21 23:49pm

Add a Solution

Comments

Richard MacCutchan 20-Oct-21 4:43am

In computers everything starts from zero. What exactly are you trying to do?

Jörgen Andersson 20-Oct-21 5:30am

Not in SQL

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-10-19T23:49:00

You could create the temp table first with an identity column

SQL

CREATE TABLE #tempFullRows (lifecycleid int,ZPartID int,ZLC int,ProccessingDate date
		, [Row_Number] int identity(1,1));

then use

SQL

insert into #tempFullRows (lifecycleid,ZPartID,ZLC,ProccessingDate) 
select lifecycleid,ZPartID,ZLC,ProccessingDate  from  
( 
    select m.lifecycleid, m.ZPartID,m.ZLC,m.ProccessingDate
    from  parts.LifeCycleMaster m inner join #TempLC t  on m.ZPartID=t.ZPartID and t.status is null
    union all
    SELECT lifecycleid, ZPartID,ZLC,ProccessingDate
    FROM #arrangeHistory
)as tempdata

As an aside, don't use SELECT * in UNIONs - list the columns you want in the order you want to avoid issues if table structures change - especially temporary tables. And do you really want union ALL - which will include duplicates

OriginalGriff · Answer 2 · 2021-10-19T23:07:00

Solution 1

Add one to it.

SQL

..., H.ProccessingDate, ROW_NUMBER() OVER(Partition by H.ZPartID ORDER BY H.ProccessingDate DESC)  + 1 AS Row_Number ...

But I suspect it will give you problems later ...

Posted 19-Oct-21 23:07pm

OriginalGriff

Comments

Jörgen Andersson 20-Oct-21 5:32am

That will still start with zero. :-D
And leave a gap to 2