У меня есть два запроса, Query1:

with cte as (
select
        dbo.Cable.*,
        row_number() over(partition by dbo.Cable.TagNo order by dbo.Cable.CableRevision desc) as rn
    from dbo.Cable 
    where (dbo.Cable.CableRevision = @CoreRevision )  
    )
select *
from cte
where rn = 1

а также Query2

with cte as (
select
        dbo.Cable.TagNo,dbo.Core.*,
        row_number() over(partition by dbo.Core.CoreNo order by dbo.Core.CoreRevision desc) as rn
    from dbo.Core INNER JOIN 
     dbo.Cable ON dbo.Cable.Id = dbo.Core.CableId
    where  (dbo.Core.CoreRevision