SQL Server 2019

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

CREATE TABLE dbo.x(ID int, ValidFrom datetime, ValidTo datetime, MyValue tinyint);

INSERT dbo.x VALUES -- notice I inserted the first two rows in a different order
(23  ,'7/7/19 13:51','9/9/19 11:22',    0),
(23  ,'7/7/19 13:51','7/7/19 13:51',    0),
(23  ,'9/9/19 11:22','9/9/19 11:23',    0),
(23  ,'9/9/19 11:23','5/14/20 23:02',   0),
(23  ,'9/3/20 20:22','9/3/20 20:23',    0),
(23  ,'9/3/20 20:23','12/31/99 0:00',   6);

-- 1.With just lag:

;WITH cte1 AS 
(
  SELECT ID, ValidFrom, ValidTo, MyValue, 
    prev = LAG(MyValue, 1) OVER (/* PARTITION BY ID */ ORDER BY ValidFrom, ValidTo) 
  FROM dbo.x WHERE ID = 23 --MyTable FOR SYSTEM_TIME ALL WHERE ID = 23
),
cte2 AS (
SELECT ID, ValidFrom, ValidTo, MyValue, is_anchor = 
   CASE WHEN prev <> MyValue or prev IS NULL THEN 1 ELSE 0 END
   FROM cte1
)
SELECT ID, ValidFrom, ValidTo, MyValue
FROM cte2 WHERE is_anchor = 1
ORDER BY ID, ValidFrom, ValidTo;

-- 2.With row_number:

;WITH cte1 AS 
(
  SELECT ID, ValidFrom, ValidTo, MyValue, 
    prev = LAG(MyValue, 1) OVER (/* PARTITION BY ID */ ORDER BY ValidFrom, ValidTo),
    rn = ROW_NUMBER() OVER (/* PARTITION BY ID */ ORDER BY ValidFrom, ValidTo)
  FROM dbo.x WHERE ID = 23 --MyTable FOR SYSTEM_TIME ALL WHERE ID = 23
),
cte2 AS (
SELECT ID, ValidFrom, ValidTo, MyValue, is_anchor = 
   CASE WHEN prev <> MyValue OR rn = 1 THEN 1 ELSE 0 END
   FROM cte1 
)
SELECT ID, ValidFrom, ValidTo, MyValue
FROM cte2 WHERE is_anchor = 1
ORDER BY ID, ValidFrom, ValidTo;

-- 3.With one less CTE (woop!):

;WITH cte1 AS 
(
  SELECT ID, ValidFrom, ValidTo, MyValue, 
    prev = LAG(MyValue, 1) OVER (/* PARTITION BY ID */ ORDER BY ValidFrom, ValidTo) 
  FROM dbo.x WHERE ID = 23 --MyTable FOR SYSTEM_TIME ALL WHERE ID = 23
)
SELECT ID, ValidFrom, ValidTo, MyValue
   FROM cte1 WHERE prev <> MyValue or prev IS NULL
  ORDER BY ID, ValidFrom, ValidTo;

ID	ValidFrom	ValidTo	MyValue
23	2019-07-07 13:51:00.000	2019-07-07 13:51:00.000	0
23	2020-09-03 20:23:00.000	1999-12-31 00:00:00.000	6

ID	ValidFrom	ValidTo	MyValue
23	2019-07-07 13:51:00.000	2019-07-07 13:51:00.000	0
23	2020-09-03 20:23:00.000	1999-12-31 00:00:00.000	6

ID	ValidFrom	ValidTo	MyValue
23	2019-07-07 13:51:00.000	2019-07-07 13:51:00.000	0
23	2020-09-03 20:23:00.000	1999-12-31 00:00:00.000	6