SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Test
        (SetName nvarchar(100),
        [Timestamp] datetime,
        Value smallint);

    INSERT INTO #Test VALUES('Alpha', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01'; --Force the value is different
    INSERT INTO #Test VALUES('Alpha', GETDATE(), 0);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Alpha', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 1);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 0);
    WAITFOR DELAY '00:00:00.01';
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 1);

    SELECT * FROM #Test


SetName
Timestamp
Value


Alpha
2022-05-23 12:29:02.517
1

Alpha
2022-05-23 12:29:02.533
0

Alpha
2022-05-23 12:29:02.550
1

Beta
2022-05-23 12:29:02.563
1

Beta
2022-05-23 12:29:02.580
1

Beta
2022-05-23 12:29:02.597
1

Gamma
2022-05-23 12:29:02.610
1

Gamma
2022-05-23 12:29:02.627
0

Gamma
2022-05-23 12:29:02.640
1


WITH CTE AS(
    SELECT SetName,
           [Timestamp], --timestamp is a deprecrated synonym for rowversion; I don't recommend it's use as a column name
           [value],
           ROW_NUMBER() OVER (PARTITION BY SetName ORDER BY Timestamp) - 
           ROW_NUMBER() OVER (PARTITION BY SetName, [Value] ORDER BY Timestamp) AS Grp
    FROM #Test)
SELECT SetName,
       [Timestamp], --timestamp is a deprecrated synonym for rowversion; I don't recommend it's use as a column name
       [value]
FROM CTE
WHERE Grp != 0
ORDER BY [Timestamp];


SetName
Timestamp
value


Alpha
2022-05-23 12:29:02.533
0

Alpha
2022-05-23 12:29:02.550
1

Gamma
2022-05-23 12:29:02.627
0

Gamma
2022-05-23 12:29:02.640
1


DROP TABLE #test;

SetName	Timestamp	Value
Alpha	2022-05-23 12:29:02.517	1
Alpha	2022-05-23 12:29:02.533	0
Alpha	2022-05-23 12:29:02.550	1
Beta	2022-05-23 12:29:02.563	1
Beta	2022-05-23 12:29:02.580	1
Beta	2022-05-23 12:29:02.597	1
Gamma	2022-05-23 12:29:02.610	1
Gamma	2022-05-23 12:29:02.627	0
Gamma	2022-05-23 12:29:02.640	1