This example is based on your original code and should produce the same result- I have reduced the alias names to make the code easier to read . This should be significantly quicker than your current query if you have a large number of records
Code:
SELECT LO.CSN_Item_Code, LO.Date_of_Purchase, LO.Unit_Price, LO.Preferred_Price_for_Costing
FROM Price_Change_Log LO INNER JOIN (SELECT CSN_Item_Code, Max(Date_of_Purchase) as MaxDate FROM Price_Change_Log GROUP BY CSN_Item_code) MD ON LO.CSN_Item_code=MD.CSN_Item_Code AND LO.Date_Of_Purchase=MD.MaxDate
check that is the case
if it is then try this code which should give you the previous unit price as well
Code:
SELECT LO.CSN_Item_Code, LO.Date_of_Purchase, LO.Unit_Price, LO.Preferred_Price_for_Costing, LI.Unit_Price
FROM (Price_Change_Log LO INNER JOIN (SELECT CSN_Item_Code, Max(Date_of_Purchase) as MaxDate FROM Price_Change_Log GROUP BY CSN_Item_code) MD ON LO.CSN_Item_code=MD.CSN_Item_Code AND LO.Date_Of_Purchase=MD.MaxDate) LEFT JOIN Price_Change_Log LI ON LO.Item_Code=LI.Item_Code
WHERE LI.Date_of_Purchase= (SELECT Max(Date_of_Purchase) FROM Price_Change_Log WHERE CSN_Item_code=LO.CSN_Item_code AND Date_of_Purchase<LO.Date_of_Purchase) OR LI.Date_of_Purchase is Null
subqueries by their nature are slow (but not as slow as domain queries) so ensure all fields used in joins and criteria are indexed (CSN_Item_Code and Date_of_Purchase in in this case)
code has been freetyped - so watch out for typo's I may have created inadvertently