SELECT MA.NAME AS MakeName , M.ID AS ModelId , M.Name AS ModelName , M.Warranty AS ModelWarranty , S.ID,S.NAME,S.StyleWarranty FROM dbo.Model M INNER JOIN dbo.Make MA ON MA.Id = M.MakeId LEFT JOIN ( SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY s1.ModelId ORDER BY s1.updatetime DESC ) rowid , s1.Id,S1.NAME,S1.MODELID,V.Value.value(‘(value/input/content)[1]‘, ‘varchar(50)‘) AS StyleWarranty FROM dbo.Style S1 INNER JOIN dbo.StylePropertyValue v ON S1.Id = v.StyleId WHERE v.PropertyId = 398 ) a WHERE rowid = 1 ) S ON S.ModelId=M.Id WHERE M.IsEnabled=1 AND M.IsRemoved=0