Select DISTINCT Acode Acode, Image=( SELECT TOP 1 i.Url from dbo.ChromeImages i where i.StyleId = cs.StyleId and i.Country = cs.Country and i.Language = cs.Language AND i.BackgroundDescription='White' AND i.ShotCode='01' AND i.ImageType='V' AND i.Width=640 ), ModelYear Year, MakeName DivDesc, ModelName ModelDesc, Trim RealTrim, BestTrimName TrimDesc, CASE when CHARINDEX(cs.ModelName,cs.Trim)>0 THEN LTRIM(RTRIM(SUBSTRING(cs.Trim,LEN(cs.ModelName)+1,100))) ELSE cs.Trim END AS trim, BodyType VarDesc, Language LngCode, MarketClass BodyStyle, MSRP = BasePriceMSRP + BasePriceDestination + 608.00 from dbo.ChromeStyles cs where ModelId in (select DISTINCT ModelId from dbo.ChromeStyles where Acode='CAC70KIS012F0'and Language='EN') and Trim<>''and BodyType=''AND cs.makename = ''and Language='en' and Country='CA' and ModelYear = Order By Trim, MSRPIncorrect syntax near the keyword 'Order'.