OUTER APPLY

a good sample for OUTER APPLY:

Returning the latest record of a child table:

SELECT ST.[SystemDetailID], ST.[SystemName], LH.LatestLoadStatus
FROM [SystemTable] AS ST
LEFT OUTER JOIN 
    (
     SELECT LHInner.LoadStatus AS LatestLoadStatus, LHInner.SystemDetailID FROM [dbo].[LoadHistory] AS LHInner
        WHERE LHInner.LoadHistoryID in 
        (
            SELECT LatestLoadHisotoryID FROM 
            (
                SELECT MAX(LoadHistoryID) as LatestLoadHisotoryID, SystemDetailID FROM [dbo].[LoadHistory]
                GROUP BY SystemDetailID
            ) l
        )   
    ) AS LH ON ST.SystemDetailID = LH.SystemDetailID

A simpler/shorter approach is:

SELECT ST.[SystemDetailID],
       ST.[SystemName],
       LH.LatestLoadStatus
FROM   [SystemTable] AS ST
       OUTER APPLY (SELECT TOP 1 *
                    FROM   [dbo].[LoadHistory] LH
                    WHERE  ST.SystemDetailID = LH.SystemDetailID
                    ORDER  BY LoadHistoryID DESC) LH  

More info: http://technet.microsoft.com/en-us/library/ms175156.aspx