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???

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???

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

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

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')