1

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!

3
  • You want to rewrite the query and add the extra column you specified ? Mar 5 at 10:47
  • @StephenMorris-Mo64 Yes, given the base data, is there a way to rewrite the query to generate an id for each block of results? Mar 5 at 10:49
  • This problem is a variant of the Gaps & Islands problem, I haven't got time right now but if you search for those terms (with SQL Server or TSQL) I'm sure you'll get some hits to get you started. Mar 5 at 10:56
2

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 <= 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
2
  • Dude, that's absolutely spot on: the answer and a blog post explaining it! Well done for even understanding my poorly explained question, let alone answering it. If I could give you extra StackExchange points I would. Much appreciated. Mar 5 at 11:33
  • Glad to help :) Mar 5 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.