>I have a master table which contains some clients. For each clients, I need to locate the most recent records in the child table. I have done that a few times but cannot remember to cleanest way to do it.
;with cte as (select P.*, C.*, row_number() over (partition by P.ClientID order by C.Date DESC) as Row from Parent P inner join Child C
on P.ClientID = C.ClientID from myTable)
select * from cte where Row =1
If it's not broken, fix it until it is.
My Blog