>>
>>SELECT T1.*, T2.*
>>FROM myTable T1
>>
>>CROSS APPLY (SELECT TOP (1) * from myTable2 T2 where T2.FK = T1.PK ORDER BY T2.SomeColumn DESC) T2
>>
>>Take a look at
>>
Including an Aggregated Column's Related Values Erik and mine blog presenting several solutions of the problem with explanations for each
>>
>>
Including an Aggregated Column's Related Values - Part 2 my blog post with use cases for the previous blog
>
>This works, thanks.
>
>I tried various combinations but I saved the CROSS APPLY syntax for the end. It seems in this case that is the only way of doing it. Or, could there be a way of doing it with INNER JOIN as well, but would probably be not as fast I guess.
You can alternatively use CTE and ROW_NUMBER() approach. In every case the performance needs to be compared to find the best approach.
If it's not broken, fix it until it is.
My Blog