How can I update column with continuous value by GROUP?

Question

I am going to update a table with continuous value based on group.

   ItemNo       Group
  ---------------------
   null         group1      
   null         group1     
   null         group2      
   null         group2       
   null         group1      
   null         group2     
   null         group1    
   null         group2      
   null         group1      

This is an example table. I am trying to set the ItemNo field with continuous value by Group. So it will be set like this.

   ItemNo       Group
  ---------------------
   1            group1      
   2            group1     
   1            group2      
   2            group2       
   3            group1      
   3            group2     
   4            group1    
   4            group2      
   5            group1      

I tried with this query by it sets the value without considering Group.

DECLARE @id int
SET @id = 0

UPDATE table
SET @id = ItemNo = @id + 1

How can I solve this problem?

Thanks.

Answer 1

;with cte as
(
select *,ROW_NUMBER() over (partition by groups order by groups) rn from YOURTABLE
)

update cte set ItemNo=rn

SQLFiddle