It's very helpful to those trying to help you out if you help us first, by supplying the SQL statements necessary to create tables with the relevant structures and inserting sample data. See the first link in my signature for how to do this.

For what you gave us, this would be:

DECLARE @test-2 TABLE (RowID INTEGER IDENTITY PRIMARY KEY CLUSTERED, subid INTEGER, [text] VARCHAR(250));

INSERT INTO @test-2 (subid, text)

VALUES (1,'processing A records' ),

(2,'1000 records processing' ),

(3,'importing A records' ),

(4,'1000 records processed' ),

(1,'processing B records' ),

(2,'500 records processing' ),

(3,'importing B records' ),

(4,'1000 records processed' );

Now, to solve your issue:

WITH cte AS

(

SELECT RowID, subid, [text], ROWID - subid AS Grp

FROM @test-2

), cte2 AS

(

SELECT DISTINCT Grp

FROM cte

), cte3 AS

(

SELECT *, RANK() OVER (ORDER BY Grp) AS ID

FROM cte2

)

SELECT cte3.ID,

subid,

[text]

FROM cte

JOIN cte3 ON cte.Grp = cte3.Grp

ORDER BY cte3.ID, subid;

What this code does is to create "data islands", where we use the sequential number RowID, and subtract from it the group number. This will, for each group, give us a constant value (in the Grp column).

Next, we need to get each of these values in a distinct list, and then utilize the RANK() function to assign them a sequential number. This gives us the ID value.

Finally, we take the data islands, and join it to the CTE (common table expression) that gives us the ID value, to show all of them.

In the above example, the results are:

ID subid text

-------------------- ----------- ------------------------

1 1 processing A records

1 2 1000 records processing

1 3 importing A records

1 4 1000 records processed

2 1 processing B records

2 2 500 records processing

2 3 importing B records

2 4 1000 records processed