Q&A about the site for enthusiasts and power users of Windows Phone OS
WITH t as ( SELECT 1 as id, 'Dan' as Name, 'N' as registered UNION SELECT 2, 'Bill' , 'N' UNION SELECT 3, 'Bob' , 'N' UNION SELECT 4, 'Dan' , 'N' UNION SELECT 5, 'Bill', 'Y' UNION SELECT 6, 'Dan', 'Y') SELECT t.id, t.name, ns.id, ns.name FROM t ns LEFT JOIN (SELECT MAX(ID) id, Name FROM t WHERE registered = 'N' GROUP BY Name) maxns ON ns.id = maxns.id and ns.registered = 'N' LEFT JOIN t ON ns.name = t.name AND t.registered = 'Y' AND maxns.id is not null WHERE ns.name IN (SELECT name FROM t WHERE registered = 'Y') ORDER BY ns.name