TSQL Group by Column with Multiple Values

Question

I have a table in SQLServer 2008r2 as below.

I want to select all the records where the [Fg] column = 1 that consecutively by [Id] order lead into value 2 for each [T_Id] and [N_Id] combination.

There can be instances where record prior to [Fg] = 2 doesn't = 1

There can be any number of records where the value of [Fg] = 1 but only one record where [Fg] = 2 for each [T_Id] and [N_Id] combination.

So for the example below, I want to select records with [Id]s (4,5) and (7,8,9 )and (19,20).

Any records for [T_Id] 3 and 4 are excluded.

Expected output

Example data set

DECLARE @Data TABLE ( Id INT IDENTITY (1,1), T_Id INT, N_Id INT, Fg TINYINT )

INSERT INTO @Data
(T_Id, N_Id, Fg)
VALUES
(1, 2, 0), (1, 2, 1), (1, 2, 0), (1, 2, 1), (1, 2, 2), (2, 3, 0), (2, 3, 1), 
(2, 3, 1), (2, 3, 2), (3, 4, 0), (3, 4, 0), (3, 4, 0), (3, 4, 2), (4, 5, 0), 
(4, 5, 1), (4, 5, 0), (4, 5, 2), (5, 7, 0), (5, 7, 1), (5, 7, 2)

user_1080354 · Answer

It can be done easily using recursive CTE:

WITH DataSource AS
(
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN @Data DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] + 1
        AND DS1.[Fg] = 2
        AND DS2.[Fg] = 1
    UNION ALL
    SELECT DS1.*
    FROM @Data DS1
    INNER JOIN DataSource DS2
        ON DS1.[T_Id] = DS2.[T_Id]
        AND DS1.[N_Id] = DS2.[N_Id]
        AND DS1.[Id] = DS2.[Id] - 1
        AND DS1.[Fg] = 1
)
SELECT *
FROM DataSource
ORDER BY Id

The idea is simple. The first part of the query gets all valid records with fg = 2 - valid means there is record before this one with fg = 1 from the same group.

Then in the recursive part we are getting all records smaller then initial ones, that has fg = 1.

user_2683491 · Answer

You cant use lag/lead because it started in SQL 2012, you will need to do something like the below.

SELECT  fg - (
        SELECT  TOP 1 fg
        FROM    table m2
        WHERE   m2.fg = m1.fg-1 OR (m2.fg = m1.fg AND m2.id < m1.id)
        ORDER BY 
                fg, id
        )
FROM table m1
ORDER BY
      fg, id

TSQL Group by Column with Multiple Values

2 Answers