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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.