Please check this query:

;WITH CTE AS

(

SELECT *,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType) Rank_ASC

,RANK()OVER(PARTITION BY GivenPartNumber_Non,vcompanyid ORDER BY SourceType DESC) Rank_DESC

FROM #notmappedsources

)SELECT GivenPartNumber_Non, vcompanyid,SourceType

FROM CTE

WHERE SourceType = 484456 AND Rank_ASC=Rank_DESC