Section 3.6.2 of the 4.1 manual has this example using a subselect: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
I use this basic syntax with max(date) alot. ----- Original Message ----- From: "João Cândido de Souza Neto" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Thursday, September 28, 2006 8:39 AM Subject: Re: Need to find last price and date product was sold > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
