select A*, B.Date, RIGHT(B.Packed,20) as RevisionLevel from tableA A inner join (select max(convert(char(20), date, 120) + convert(char(20),RevisionLevel) as Packed, max(Date) as Date, PartNo from TableB B group by PartNo) B on A.PartNo = B.PartNo and A.RevisionLevel <> right(B.Packed,20)Perhaps will not be quicker than the original 3 steps variation either.