I need to write a query that will pull my products and price. However each of the products have multiple prices and I want the price with the most recent date. I have tried the following query but it doesn't work. Can someone assist me. I'm fairly new to sql queries.
SELECT SDModelProdPrices.EffectiveDate, Products.ProdCode, Products.longDescr, SDModelProdPrices.UnitPrice, SDModels.Descr AS 'Model' FROM SPLive.dbo.ProdSubCategories ProdSubCategories, SPLive.dbo.Products Products, SPLive.dbo.SDModelProdPrices SDModelProdPrices, SPLive.dbo.SDModelProds SDModelProds, SPLive.dbo.SDModels SDModels, SPLive.dbo.Subdivisions Subdivisions WHERE SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdID AND SDModelProds.SDModelID = SDModels.SDModelID AND Products.ProductID = SDModelProds.ProductID AND ProdSubCategories.ProdSubCatID = Products.ProdSubCatID AND Subdivisions.SubdivisionID = SDModels.SubdivisionID AND ProdSubCategories.Descr = 'Value Series Products' AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate) GROUP BY Products.ProdCode, Products.longDescr, SDModelProdPrices.UnitPrice, SDModels.Descr, SDModelProds.Discontinued HAVING SDModels.Descr = 'Bedford' AND SDModelProds.Discontinued = 0 ORDER BY Products.ProdCode