By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE history(
ID INT,
Column1 VARCHAR(6),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
CreatedDate DATETIME,
UpdatedDate DATETIME
);
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2022/02/02 18:39:38', '2022/03/29 14:25:24');
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2022/01/05 10:45:50', '2022/02/02 18:39:38');
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2022/01/03 12:11:47', '2022/01/05 10:45:50');
INSERT INTO history VALUES(1122,'T1','In Progress', 'Yes', '2021/12/13 21:43:44', '2022/01/03 12:11:47');
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2021/02/17 14:12:15', '2021/12/13 21:43:44');
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2020/12/22 14:38:32', '2021/02/17 14:12:15');
INSERT INTO history VALUES(1122,'T1','In Progress', NULL, '2020/12/17 18:38:38', '2020/12/22 14:38:32');
INSERT INTO history VALUES(1122,'T3','Ready', NULL, '2020/03/30 14:35:18', '2020/12/17 18:38:38');
INSERT INTO history VALUES(1122,NULL,'Ready', NULL, '2019/09/04 18:33:24', '2020/03/30 14:35:18');
INSERT INTO history VALUES(1122,'T2','Ready', NULL, '2019/01/07 11:07:39', '2019/09/04 18:33:24');
INSERT INTO history VALUES(1122,'T2','Ready', NULL, '2018/09/17 14:31:17', '2019/01/07 11:07:39');
INSERT INTO history VALUES(1122,'T0','Ready', NULL, '2018/08/28 14:31:39', '2018/09/17 14:31:17');
INSERT INTO history VALUES(1122,'T0','Ready', NULL, '2018/02/13 14:48:44', '2018/08/28 14:31:39');
13 rows affected
SELECT ID,Column1,Column2,Column3, min(createddate) CreatedDate, max(updateddate) UpdatedDate
FROM (
select *,
ROW_NUMBER() over (partition by ID order by createddate) -
ROW_NUMBER() over (partition by ID,Column1,Column2,Column3 order by createddate) grp
from history
) t1
GROUP BY grp,ID,Column1,Column2,Column3
ORDER BY CreatedDate DESC
| ID | Column1 | Column2 | Column3 | CreatedDate | UpdatedDate |
|---|---|---|---|---|---|
| 1122 | T1 | In Progress | null | 2022-01-03 12:11:47.000 | 2022-03-29 14:25:24.000 |
| 1122 | T1 | In Progress | Yes | 2021-12-13 21:43:44.000 | 2022-01-03 12:11:47.000 |
| 1122 | T1 | In Progress | null | 2020-12-17 18:38:38.000 | 2021-12-13 21:43:44.000 |
| 1122 | T3 | Ready | null | 2020-03-30 14:35:18.000 | 2020-12-17 18:38:38.000 |
| 1122 | null | Ready | null | 2019-09-04 18:33:24.000 | 2020-03-30 14:35:18.000 |
| 1122 | T2 | Ready | null | 2018-09-17 14:31:17.000 | 2019-09-04 18:33:24.000 |
| 1122 | T0 | Ready | null | 2018-02-13 14:48:44.000 | 2018-09-17 14:31:17.000 |