finding the most recently updated record with multiple date columns
You can get the aggregates, MAX(Date) in this case, by using GROUP BY:

Quoted from MS Docs:


  A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.


SELECT
    SerialNumber,
    MAX(Repair_Date) as Repair_Date,
    MAX(Inspection_Date) as Inspection_Date,
    MAX(Replaced_Date) as Replaced_Date 
FROM
    YourTable
GROUP BY 
    SerialNumber