Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence
WITH Ov As ( SELECT 1 as ID, 'Name1' as Name UNION SELECT 2, 'Name2' UNION SELECT 3, 'Name3' UNION SELECT 4, 'Name4' UNION SELECT 5, 'Name5' ) , St AS ( SELECT 1 as ID, 1 as RelId, 'Set Date 1.1' as Date UNION SELECT 2, 1, 'Set Date 1.2' UNION SELECT 3, 1, 'Set Date 1.3' UNION SELECT 4, 2, 'Set Date 2.1' UNION SELECT 5, 2, 'Set Date 2.2' UNION SELECT 6, 3, 'Set Date 3.1' UNION SELECT 7, 4, 'Set Date 4.1') select Ov.Id , Ov.Name, NewSt.MaxDate FRom Ov INNER JOIN St ON St.RelId = Ov.ID INNER JOIN ( select St.RelId, MAX(St.Date)as MaxDate from St group by St.RelId ) as NewSt ON St.RelId = NewSt.RelId