SQL Server 2019

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

CREATE TABLE #Test
        (ID int IDENTITY,
        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);
    --These will all have the same value for Timestamp
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    INSERT INTO #Test VALUES('Beta', GETDATE(), 1);
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 1);
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 0);
    INSERT INTO #Test VALUES('Gamma', GETDATE(), 1);

    SELECT * FROM #Test


ID
SetName
Timestamp
Value


1
Alpha
2022-05-23 13:50:03.910
1

2
Alpha
2022-05-23 13:50:03.923
0

3
Alpha
2022-05-23 13:50:03.940
1

4
Beta
2022-05-23 13:50:03.940
1

5
Beta
2022-05-23 13:50:03.940
1

6
Beta
2022-05-23 13:50:03.940
1

7
Gamma
2022-05-23 13:50:03.940
1

8
Gamma
2022-05-23 13:50:03.940
0

9
Gamma
2022-05-23 13:50:03.940
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,ID) - 
           ROW_NUMBER() OVER (PARTITION BY SetName, [Value] ORDER BY Timestamp,ID) 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 13:50:03.923
0

Alpha
2022-05-23 13:50:03.940
1

Gamma
2022-05-23 13:50:03.940
0

Gamma
2022-05-23 13:50:03.940
1


DROP TABLE #test;

ID	SetName	Timestamp	Value
1	Alpha	2022-05-23 13:50:03.910	1
2	Alpha	2022-05-23 13:50:03.923	0
3	Alpha	2022-05-23 13:50:03.940	1
4	Beta	2022-05-23 13:50:03.940	1
5	Beta	2022-05-23 13:50:03.940	1
6	Beta	2022-05-23 13:50:03.940	1
7	Gamma	2022-05-23 13:50:03.940	1
8	Gamma	2022-05-23 13:50:03.940	0
9	Gamma	2022-05-23 13:50:03.940	1