From: Peter Brawley
Date: September 28 2006 3:12pm
Subject: Re: Need to find last price and date product was sold
List-Archive: http://lists.mysql.com/mysql/202179
Message-Id: <451BE66D.2080903@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mike,

>What I need to do is find the last price_sold for each product_code. 

SELECT 
  t1.product_code,t1.date_sold,t1.price_sold
FROM trans AS t1
LEFT JOIN trans AS t2 
  ON t1.product_code = t2.product_code 
  AND t1.price_sold < t2.price_sold
WHERE t2.product_code IS NULL
ORDER BY t1.product_code;

There's a bit of discussion at http://www.artfulsoftware.com/queries.php#7/

PB

-----

mos wrote:
> 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
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006