SQLTeam.com | Weblogs | Forums

Increment values in one column based on another column


#1

I have two columns Label and Rev
I want to increment the Rev column by 1 based on the label column.

SET @Rev=(SELECT
ROW_NUMBER() OVER(PARTITION BY [Label]
ORDER BY [Rev] ) as Rev
FROM dbo.mydatabase)

but this does not work.
Any suggestions???


#2

What is the criteria for the increment? What is the expected output?


#3

Do you just want to SELECT the values or do you want to UPDATE the table with the sequential values?


#4

I think he means for the label group
declare @Numbers table (numero int not null) ;

WITH Nums(numero) AS
(SELECT 1 AS Number
UNION ALL
SELECT numero+1 FROM Nums where numero < 10000 --you can make this smarter by grabbing the min max
)
insert into @Numbers(numero)
select numero from Nums option(maxrecursion 10000)

declare @anusha table(label varchar(50), Rev int)
insert into @anusha
select 'xyz', 1
union
select 'xyz', 2
union
select 'xyz', 3
union
select 'ab1', 1
union
select 'ab1', 2
union
select 'ab1', 3
union
select 'ab1', 4
union
select 'fg4', 1

--insert into @anusha
select top 1 'fg4', numero
from @anusha a
cross apply @Numbers b
where label = 'fg4'
and numero > (select max(rev) from @anusha where label = 'fg4')