0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited jul 13 anonymous

created feb 27 12 conradfrix