How can I update column with continuous value by GROUP?

user_11524321 0
Viewed 138

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.

tsql sql-server sql
2 Answers
;with cte as
(
select *,ROW_NUMBER() over (partition by groups order by groups) rn from YOURTABLE
)

update cte set ItemNo=rn

SQLFiddle

user_62601570

SQL tables represent unordered sets. Your assignment seems to depends on the ordering of the rows. In order to do what you want, you require an ordering column.

If you have one, you can use gaps-and-islands techniques and updatable CTEs:

with toupdate as (
      select t.*,
             row_number() over (partition by group, seqnum - seqnum_g order by <ordering col>) as new_itemno
      from (select t.*,
                   row_number() over (order by <ordering col>) as seqnum,
                   row_number() over (partition by group order by <ordering col>) as seqnum_g
            from t
           ) t
     )
update toupdate
    set itemno = new_itemno;
user_11440350
Related