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" <[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]
