SQL Server 2017

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

create table t1(id int, value int, rev varchar(2))
insert into t1(id,value,rev) values(1,1001,'01')
insert into t1(id,value,rev) values(1,1002,'02')
insert into t1(id,value,rev) values(1,1001,'03')
insert into t1(id,value,rev) values(1,1002,'03')
insert into t1(id,value,rev) values(2,1003,'02')
insert into t1(id,value,rev) values(2,1004,'03')
insert into t1(id,value,rev) values(3,1003,'01')
insert into t1(id,value,rev) values(3,1005,'02')
insert into t1(id,value,rev) values(3,1005,'03')
insert into t1(id,value,rev) values(4,1002,'01')
10 rows affected

select id, value, rev from 
(
select * , lead(value) over(partition by id order by rev) as val1,
lag(value) over(partition by id order by rev) as val2
from t1
)A 
where (value<>val2 or value<>val1)


id
value
rev


1
1001
01

1
1002
02

1
1001
03

1
1002
03

2
1003
02

2
1004
03

3
1003
01

3
1005
02