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.