Q&A about the site for music historians, critics, and fans
WITH A As ( SELECT 1 as ID, 100 rank , 'Name1' as Name UNION SELECT 1, 45, 'Name2' UNION SELECT 2, 60, 'Name3' UNION SELECT 2, 42, 'Name4' UNION SELECT 2, 88, 'Name5' ) , B AS ( SELECT 1 as ID, 'fn1' as FileName UNION SELECT 2 , 'fn2') , CTE AS ( SELECT ID, RANK, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RANK DESC) rn FROM A ) SELECT b.Id , b.FileName,cte.Name FROM b INNER JOIN cte ON b.id = cte.id and cte.rn = 1