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 can also be done with a CROSS APPLY or an INNER APPLY join.

Last modified by Mohit @ 4/4/2025 8:16:29 AM