Reordering Subgroup

USE [db] GO --delcare variables DECLARE @i int DECLARE @lastrow int DECLARE @curPoc int DECLARE @curDir int DECLARE @lastDir int DECLARE @order int DECLARE @pocs AS TABLE ( rownum int IDENTITY(1, 1) PRIMARY KEY NOT NULL , pocID int NOT NULL , fk_directorateID int NOT NULL ) -- this builds a var table that is pre-sorted according to criteria INSERT INTO @pocs ( pocID, fk_directorateID ) SELECT A.pocId , A.fk_directorateID FROM t_poc A (NOLOCK) WHERE A.isArchived = 0 ORDER BY A.fk_directorateID , A.orderBy , A.pocID SELECT @lastrow = max(A.rownum) FROM @pocs A SET @i = 1 SET @lastDir = 0 SET @order = 0 --loop through all POCs in POC table WHILE @i <= @lastrow BEGIN SELECT @curPoc = A.pocID , @curDir = A.fk_directorateID FROM @pocs A WHERE A.rownum = @i IF @lastDir = @curDir BEGIN SET @order = @order + 1 END ELSE BEGIN SET @order = 0 SET @lastDir = @curDir END UPDATE t_poc SET orderBy = @order WHERE pocID = @curPoc SET @i = @i + 1 END GO