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

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

OriginalGriff · Answer 1 · 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

CHill60 · Answer 2 · 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

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

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

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us