Please, try to do the follow select, i think it´ll works fine. select product_code, max(date_sold), price_sold from trans group by product_code order by product_code
"mos" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] > This should be easy but I can't find a way of doing it in 1 step. > > I have a Trans table like: > > Product_Code: X(10) > Date_Sold: Date > Price_Sold: Float > > Now there will be 1 row for each Product_Code, Date combination. So over > the past year a product_code could have over 300 rows, one row for each > day it was sold. There are thousands of products. > > What I need to do is find the last price_sold for each product_code. Not > all products are sold each day so a product might not have been sold for > weeks. > > The only solution I've found is to do: > > drop table if exists CurrentPrices; > create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as > Date), -1.0 Price_Sold from Trans group by Prod_Code; > alter table CurrentPrices add index ix_ProdCode (Prod_Code); > update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and > T.Date_Sold=CP.Date_Sold; > > Is there a way to shorten this? It may take 2-3 minutes to execute. I > don't really need a new table as long as I get the Prod_Code and the last > Date_Sold. > > TIA > Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
