You haven't stated which version of SQL Server you are using, but from SQL 2016 there a "Temporal tables" designed to recreate the data at a specific time - see
SQL 2016 – Temporal Tables – What are they and how do you set one up? – SQL Server Premier Field Engineer Blog[
^]
If you are using earlier versions of SQL Server then why not store the last updated datetime on the table itself instead of on a separate audit table?
Alternatively, capture the latest audit entry per item in a temporary table or CTE e.g.(NB untested and may contain typing errors)
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ChangedOn DESC) AS rn
FROM L_Audit where TableName='BaseData'
)
select [ID],[Supplier],CTE.OldValue,
CASE WHEN fieldname='Supplier' THEN CTE.OldValue ELSE [Supplier] END as [C_Supplier] from [dbo].BaseData BD
LEFT JOIN CTE ON L_Audit.ID=CTE.[ID]
WHERE rn = 1