SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE test (
    record_number INT,
    col1 INT,
    col2 INT
);

INSERT INTO test (record_number, col1, col2) VALUES
(1, 123, 1),
(2, 456, 1),
(3, 789, 2),
(4, 147, 2),
(5, 258, 3),
(6, 852, 4),
(7, 963, 2),
(8, 213, 1);

8 rows affected

select
    record_number,col1,col2,
    case 
        when col2 IN (1, 3) then col1
        else first_value(col1) 
       over (partition by grp order by record_number
            rows between unbounded preceding and current row
        )
    end as col3
from (
    select *,
        sum(case  when col2 in (1, 3) then 1 else 0  end) 
        over (order by record_number) AS grp
    from test
) t
order by record_number;



record_number
col1
col2
col3


1
123
1
123

2
456
1
456

3
789
2
456

4
147
2
456

5
258
3
258

6
852
4
258

7
963
2
258

8
213
1
213

record_number	col1	col2	col3
1	123	1	123
2	456	1	456
3	789	2	456
4	147	2	456
5	258	3	258
6	852	4	258
7	963	2	258
8	213	1	213

record_number	col1	col2	col3
1	123	1	123
2	456	1	456
3	789	2	456
4	147	2	456
5	258	3	258
6	852	4	258
7	963	2	258
8	213	1	213

record_number	col1	col2	col3
1	123	1	123
2	456	1	456
3	789	2	456
4	147	2	456
5	258	3	258
6	852	4	258
7	963	2	258
8	213	1	213