SQL Server 2017

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

CREATE TABLE Projects(
   ProjectID INT,
   ProjectName VARCHAR(5) 
);

CREATE TABLE Comparisons(
   CompareID INT,
   ProjectID INT,
   AssetID INT
);

CREATE TABLE Assets(
   AssetID INT,
   AssetName VARCHAR(5) 
);

insert into Projects values (1,'A');         
insert into Projects values (2,'B');         
insert into Projects values (3,'C');

insert into Comparisons values (101,1,201);       
insert into Comparisons values (102,1,202);       
insert into Comparisons values (103,2,203);       
insert into Comparisons values (104,3,204);       
insert into Comparisons values (105,1,205);       
insert into Comparisons values (106,2,206);       
insert into Comparisons values (107,1,207);

insert into Assets values (201,'ZZZ');
insert into Assets values (202,'YYY');
insert into Assets values (203,'XXX');
insert into Assets values (204,'WWW');
insert into Assets values (205,'VVV');
insert into Assets values (206,'UUU');
insert into Assets values (207,'TTT');

SELECT Projects.ProjectName, 
       CompareIDMin,
       Assets.AssetName
FROM (
   select ProjectID,Min(CompareID) AS CompareIDMin,MIN(AssetID) AS AssetIDMin
   FROM Comparisons
   GROUP BY ProjectID
) as c
INNER JOIN Projects ON c.ProjectID = Projects.ProjectID
INNER JOIN Assets ON Assets.AssetID = c.AssetIDMin

ProjectName	CompareIDMin	AssetName
A	101	ZZZ
B	103	XXX
C	104	WWW