Section 3.6.2 of the 4.1 manual has this example
using a subselect:
SELECT article, dealer, price
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" <joao@stripped>
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" <mos99@stripped> escreveu na mensagem
> news:184.108.40.206.2.20060927230926.0353a8f8@ style="color:#666">stripped...
> > 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
> > Date_Sold.
> > TIA
> > Mike
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: