|Most likely the reason is in your group by section. You group on two columns so you'll get as many rows as there are valid combinations present. But then on your join you use only one column (SerialNumber).
If you want the latest record based on maximum ID and the maximum of id should be fetched based on serialnumber, it may be simpler to use correlated scalar at select portion (there may be typos):
From Results r1
WHERE Details.SN = r1.SerialNumber
AND ID = (SELECT MAX(ID)
FROM Results r2
WHERE Details.SN = r1.SerialNumber)
FROM Details;Hope this helps,
The need to optimize rises from a bad design.