Problems with select(max).... query, please help 1

[RichardTisdall]

Need a little help here! I have a table 'AllCostPriceData' that contains product codes and prices. From time to time prices change. Rather than delete obsolete prices they are retained in the table for historical purposes so there is a further column in my table 'effective_date' It looks something like this

Code Price Effective_Date
ABC001 0.99 01/01/2004
ABC001 1.09 01/02/2005
ABC002 2.59 01/01/2005
ABC003 1.29 01/06/2003
ABC003 1.39 01/07/2004
ABC003 1.59 01/01/2005
ABC004 0.79 01/02/2004
ABC004 1.19 01/02/2005

What I want to do is get the current/latest price of all products. I have no problem doing this where I specify a product code e.g.

SELECT Max(AllCostPriceData.Effective_Date) , 
       AllCostPriceData.Price_Distributor, 
       AllCostPriceData.Code  
FROM Reference.dbo.AllCostPriceData 
where Effective_Date =              (Select max(Effective_Date) 
from Reference.dbo.AllCostPriceData             
where AllCostPriceData.Code ='ABC001')  and                   
AllCostPriceData.Code ='ABC001'  
GROUP BY  AllCostPriceData.Price_Distributor,AllCostPriceData.Code

This works fine and returns me the latest price for the specific code. I run into problems when I want to get the lates price for each record. I have tried this

SELECT Max(AllCostPriceData.Effective_Date) , 
       AllCostPriceData.Price_Distributor, 
       AllCostPriceData.Code  
FROM Reference.dbo.AllCostPriceData AllCostPriceData 

GROUP BY  AllCostPriceData.Price_Distributor,AllCostPriceData.Code

And for a bit I thought it worked but I seem to have various prices for various codes rather than the latest price for each code. I am sure I'm doing something incredibly foolish but if someone can help I'd be really happy.

Cheers

Richard

 

[Pattycake245]

Try this:

SELECT a.Code, a.Price_Distributor, a.Effective_Date
FROM Reference.dbo.AllCostPriceData a
where a.Effective_Date = (select max(Effective_Date) from Reference.dbo.AllCostPriceData t where a.Code=t.Code)
GROUP BY a.Code, a.Price_Distributor, a.Effective_Date

Tim

 

[RichardTisdall]

Tim

Thanks, works a treat!

Richard

 

[Pattycake245]

No problem