SELECT * FROM(
SELECT
[SPID]
,[PH1]
,[PH1_Code]
,[ProjectName]
,ROW_NUMBER() OVER(PARTITION BY [SPID] ORDER BY [SPID]) RowNum
FROM(
SELECT
a.[SPID]
,b.[ProjectName]
,b.[PH1] AS [PH1_Code]
,e.[Description] AS [PH1]
FROM [dbo].[CAE_SPAndTask] a
LEFT JOIN [dbo].[CAE_Task] b ON b.[CAENumber]= a.[CAENumber]
LEFT JOIN [dbo].[CAE_PH1PH2] e ON e.[ProductHierarchy] = b.[PH1]
)t4
GROUP BY [SPID],[ProjectName],[PH1_Code],[PH1]
)t5
WHERE RowNum = 1