Re: Need to find last price and date product was sold

[mos]

At 07:39 AM 9/28/2006, you wrote:
>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

Unfortunately that doesn't guarantee that the price_sold will match the row 
with the max(date_sold).
Someone gave me the solution via email using a subselect that works well.

It goes something like this:

select t1a.account, maxdate, amount from (select account, max (date_xact) 
maxdate from transactions t1 group by account) t1a left
join transactions t2 on t1a.account=t2.account and
maxdate=t2.date_xact order by t1a.account;


Mike



>"mos" <mos99@stripped> escreveu na mensagem
>news:6.0.0.22.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 last
> > Date_Sold.
> >
> > TIA
> > Mike
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1