>I am looking for an example of a INNER JOIN clause using a TOP syntax. Basically, as this is not a one to one relationship, I need to get the TOP most record, based on a condition, of the INNER JOIN table I am using. So, this is simple. I would end up with MIN(Table.PrimaryKey) and then Table.MyField.
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
If it's not broken, fix it until it is.
My Blog