Q&A for movie and tv enthusiasts
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' UNION SELECT 3 , 'fn3') SELECT DISTINCT b.*, a1.name FROM a a1 right OUTER JOIN b ON b.id = a1.id LEFT JOIN a a2 ON a2.id = a1.id AND a2.rank > a1.rank WHERE b.FileName in ('fn1','fn3') and a2.id IS NULL