MS SQL query using Max

[mhaskell]

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

[tesuji]

...
WHERE...
AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
...

Aggregate functions are not allowed in WHERE clause. So aggregate functions cannot be used for selecting specific rows. However, you can use them in HAVING clause. For MS Sql Server one solution to get the most recent date values is:

select TOP 1 ......
......
ORDER BY SDModelProdPrices.EffectiveDate desc

krs,
tesu

[mhaskell]

I think I'm closer would you mind to help me further?

[mhaskell]

Data information
ProductA 12.00 12/10/2002
ProductA 12.50 01/15/2005
ProductA 12.01 01/02/2008
ProductB 15.12 12/01/2005
ProductB 16.00 01/09/2008
ProductB 16.24 01/15/2008

I want to return

ProductA 12.01 01/02/2008
ProductB 16.24 01/15/2008

I have tried this query it returns the oldest date of all of the products

SELECT TOP 1 Product, price, date
FROM table
WHERE SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdPriceID
GROUP BY product, date
ORDER BY date

Return Data
ProductA 12.00 12/10/2002

[tesuji]

. . . ORDER BY date DESC

[huangzhi]

create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005' union all
select 'ProductA', 12.01, '01/02/2008' union all
select 'ProductB', 15.12, '12/01/2005' union all
select 'ProductB', 16.00, '01/09/2008' union all
select 'ProductB', 16.24, '01/15/2008'

select	T.*
  from	#tmp T
  where Date = (select max(Date) from #tmp X where X.Code = T.Code)

drop table #tmp