SQLTeam.com | Weblogs | Forums

Problem with counting raws


#1

hello,
i have a table in which i have to fill teh column Count. here is how it should be at the end:
image.
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]
begin

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

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!


#2

I believe, ROW_NUMBER function is suitable for this.


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

insert into @vt_table(col,id,mycount)
values('audefault',810428163,null),('audefault',810428163,null),('audefault',810428163,null),('audefault',136441,null);

;with cte
AS
(
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

#4

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


#5

You're welcome!
este my pleasure!