T-SQL: Assign identifier to block of results
Referencing SQL Server Lag Function to Group Table Rows on Column Value Changes:
--demo setup
drop table if exists Table1;
go

CREATE TABLE Table1
    ([Id] int, [Title] varchar(7), [BlockName] varchar(3), [Order] int)
;
    
INSERT INTO Table1
    ([Id], [Title], [BlockName], [Order])
VALUES
    (100, 'Title A', 'Foo', 1),
    (101, 'Title B', 'Foo', 2),
    (102, 'Title C', 'Foo', 3),
    (103, 'Title D', 'Bar', 4),
    (104, 'Title E', 'Bar', 5),
    (105, 'Title F', 'Foo', 6)
;

--solution
;WITH cte
AS (
    SELECT id
        ,Title
        ,BlockName
        ,[Order]
        ,lag(BlockName, 1, '') OVER (
            ORDER BY [Order]
            ) previous
        ,CASE 
            WHEN (
                    lag(BlockName, 1, '') OVER (
                        ORDER BY [Order]
                        )
                    ) = BlockName
                THEN 0
            ELSE 1
            END ischange
    FROM Table1
    )
    ,tbl
AS (
    SELECT t.*
        ,(
            SELECT sum(ischange)
            FROM cte
            WHERE id &lt;= t.id
            ) groupno
    FROM cte t
    )
SELECT Id
    ,Title
    ,BlockName
    ,[Order]
    ,groupno AS BlockGroup
FROM tbl


Id          Title   BlockName Order       BlockGroup
----------- ------- --------- ----------- -----------
100         Title A Foo       1           1
101         Title B Foo       2           1
102         Title C Foo       3           1
103         Title D Bar       4           2
104         Title E Bar       5           2
105         Title F Foo       6           3