T-SQL: Assign identifier to block of results I'm not sure exactly how to phrase this question but... I'm working with an existing database and the structure is something I can't change. I want to write a query which assigns a unique id to each group of a the same block value in the results returned by my query.
An example might be helpful! If I have a SQL statement such as
SELECT 
   Id, 
   Title, 
   Block, 
   Order 
FROM 
   TableName 
ORDER BY Order ASC

that returns the result:




Id
Title
BlockName
Order




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




Is there any fairly straightforward way to generate a result which gives a unique id to each block of results, something like:




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




I hope this is clear enough :) Thanks in advance for any help!
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