SQLTeam.com | Weblogs | Forums

Problem with counting raws


i have a table in which i have to fill teh column Count. here is how it should be at the end:
the formula to fill the column Count should be:
if ID1=ID2 then Count =Count +1 else 1
for example for the ID=801428163, ID6<>ID5 so it 1; ID7=ID6 so count is 1+1=2 end so on.

i have tried something like this but it doesnt work:

declare @ val real
declare @ org_ID nvarchar

set @val=1
set @org_ID=[ID]

update table
set [Count]= case when @org_ID=@org_ID-1 then @val=@val+1
else ' '

Something is wrong in this code and i cant figure it out what, besides the fact that it gives me an errror like
the syntax is incorrect near '=', i have a feeling that even if i wouldnt have received this error the result
would not have been the one which i look for.

thank you in advance , any suggestion would be highly appreciated!


I believe, ROW_NUMBER function is suitable for this.

declare @vt_table table
(col varchar(50) not null,
id int not null,
MyCount int null)

insert into @vt_table(col,id,mycount)

;with cte
select col ,id,mycount, row_number() over(partition by id order by id) as rn
from @vt_table

update c
set mycount = rn
from cte as c

select * from @vt_table


thank you very much, the raw_number is exactly what i needed :smiley: you were very helpful


You're welcome!
este my pleasure!