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 #Shipment (Shipment_ID int, Shipment_ID_Master int)
insert into #Shipment select 4767 ,               4767 
insert into #Shipment select 88359 ,              28431

CREATE TABLE #Factory (Factory_ID int, Shipment_ID int)
insert into #Factory select 338161 ,        4767 
insert into #Factory select 1178567 ,       88359
insert into #Factory select 1178568 ,       88359

CREATE TABLE #Coverage (Factory_ID int, Public_ID int, Revision int)
insert into #Coverage select 338161 ,      2354 ,        2 
insert into #Coverage select 1178567 ,     32436 ,        4
insert into #Coverage select 1178568 ,      2354 ,        3

SELECT * FROM #Shipment s
SELECT * FROM #Factory f
SELECT * FROM #Coverage c


Shipment_ID
Shipment_ID_Master


4767
4767

88359
28431




Factory_ID
Shipment_ID


338161
4767

1178567
88359

1178568
88359




Factory_ID
Public_ID
Revision


338161
2354
2

1178567
32436
4

1178568
2354
3


;WITH cte AS
(
	SELECT s.Shipment_ID, s.Shipment_ID_Master, f.Factory_ID, c.Public_ID, c.Revision, row_number() OVER (PARTITION BY s.Shipment_ID ORDER BY c.Public_ID desc) AS rn
	FROM #Shipment s
	JOIN #Factory f	ON f.Shipment_ID = s.Shipment_ID
	JOIN #Coverage c ON c.Factory_ID = f.Factory_ID
)
SELECT c.Shipment_ID, c.Shipment_ID_Master, c.Factory_ID, c.Public_ID, c.Revision 
FROM cte c WHERE rn = 1


Shipment_ID
Shipment_ID_Master
Factory_ID
Public_ID
Revision


4767
4767
338161
2354
2

88359
28431
1178567
32436
4