Selecting a single record from a joined table

Selecting a single record from a joined table that would usually have multiple records.

In this example, the ItemMaster_hist table is a history table that holds all changes made to the ItemMaster table.

The [HistID] field is the identity column on the history table.

The [dtToHistory] is the date/time that the history record was created.

The field a.UnitPrice is the current value for when the record was saved. The field b.UnitPrice is the previously saved value.

SELECT a.HistID, b.HistID
       , a.UnitPrice, b.UnitPrice
       , a.*
from ItemMaster_hist a
inner join ItemMaster_hist b on a.ProductCode = b.ProductCode 
       and b.lHistID = ISNULL(
              (select top 1 c.lHistID 
                     from ItemMaster_hist c 
                     where c.ProductCode = a.ProductCode 
                            and c.dtToHistory < a.dtToHistory 
                     order by c.dtToHistory desc)
              , 0)
where a.ProductCode = 'XYZ'

This same result can be achieved with a cross apply or outer apply.