SQL Server 2019

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

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
 Mar  4 2020 00:59:26 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table material(ID varchar(20), REV varchar(20),name varchar(20),Description varchar(20),curr varchar(20));

insert into material values('211-32','001','Screw 1.0','Used in MAT 1','READY');
insert into material values('211-32','002','Screw 2 plus','can be Used-32','WITHDRAWN');
insert into material values('212-41','001','Bolt H1','Light solid','READY');
insert into material values('212-41','002','BOLT H2+Form','Heavy solid','READY');
insert into material values('101-24','001','HexHead 1-A','NOR-1','READY');
insert into material values('101-24','002','HexHead Spl','NOR-22','READY');
insert into material values('423-98','001','Nut Repair spare','NORM1','READY');
insert into material values('423-98','002','Nut Repair Part-C','NORM2','WITHDRAWN');
insert into material values('423-98','003','Nut SP-C','NORM2+NORM1','NULL');
insert into material values('654-01','001','Bar','Specific only','WITHDRAWN');
insert into material values('654-01','002','Bar rod-S','Designed+Spe','WITHDRAWN');
insert into material values('654-01','003','Bar OPG','Hard spec','NULL');
12 rows affected

select * from material


ID
REV
name
Description
curr


211-32
001
Screw 1.0
Used in MAT 1
READY

211-32
002
Screw 2 plus
can be Used-32
WITHDRAWN

212-41
001
Bolt H1
Light solid
READY

212-41
002
BOLT H2+Form
Heavy solid
READY

101-24
001
HexHead 1-A
NOR-1
READY

101-24
002
HexHead Spl
NOR-22
READY

423-98
001
Nut Repair spare
NORM1
READY

423-98
002
Nut Repair Part-C
NORM2
WITHDRAWN

423-98
003
Nut SP-C
NORM2+NORM1
NULL

654-01
001
Bar
Specific only
WITHDRAWN

654-01
002
Bar rod-S
Designed+Spe
WITHDRAWN

654-01
003
Bar OPG
Hard spec
NULL


with cte as (
select *,dense_rank() over (partition by id order by rev desc) as DR ,
lead(curr) over (partition by id order by rev desc) LEAD_CURR
from material )
select * from cte where DR = 1 and curr='READY'
union all
select * from cte where LEAD_CURR='READY' and DR=2
union all
select * from cte where LEAD_CURR='READY' and DR=3


ID
REV
name
Description
curr
DR
LEAD_CURR


101-24
002
HexHead Spl
NOR-22
READY
1
READY

212-41
002
BOLT H2+Form
Heavy solid
READY
1
READY

423-98
002
Nut Repair Part-C
NORM2
WITHDRAWN
2
READY

ID	REV	name	Description	curr
211-32	001	Screw 1.0	Used in MAT 1	READY
211-32	002	Screw 2 plus	can be Used-32	WITHDRAWN
212-41	001	Bolt H1	Light solid	READY
212-41	002	BOLT H2+Form	Heavy solid	READY
101-24	001	HexHead 1-A	NOR-1	READY
101-24	002	HexHead Spl	NOR-22	READY
423-98	001	Nut Repair spare	NORM1	READY
423-98	002	Nut Repair Part-C	NORM2	WITHDRAWN
423-98	003	Nut SP-C	NORM2+NORM1	NULL
654-01	001	Bar	Specific only	WITHDRAWN
654-01	002	Bar rod-S	Designed+Spe	WITHDRAWN
654-01	003	Bar OPG	Hard spec	NULL