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 m.*
from (select m.*,
             row_number() over (partition by id order by rev desc) as seqnum
      from material m
      where curr is not null and curr not in ( 'WITHDRAWN', 'NULL' ) 
     ) m
where seqnum = 1;


ID
REV
name
Description
curr
seqnum


101-24
002
HexHead Spl
NOR-22
READY
1

211-32
001
Screw 1.0
Used in MAT 1
READY
1

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

423-98
001
Nut Repair spare
NORM1
READY
1


select m.*
from material m
where m.rev = (select max(m2.rev)
               from material m2
               where m2.id = m.id and
                     curr is not null and curr not in ( 'WITHDRAWN', 'NULL' ) 
              )


ID
REV
name
Description
curr


211-32
001
Screw 1.0
Used in MAT 1
READY

212-41
002
BOLT H2+Form
Heavy solid
READY

101-24
002
HexHead Spl
NOR-22
READY

423-98
001
Nut Repair spare
NORM1
READY

ID	REV	name	Description	curr	seqnum
101-24	002	HexHead Spl	NOR-22	READY	1
211-32	001	Screw 1.0	Used in MAT 1	READY	1
212-41	002	BOLT H2+Form	Heavy solid	READY	1
423-98	001	Nut Repair spare	NORM1	READY	1