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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
