List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:September 28 2006 3:49pm
Subject:Re: Need to find last price and date product was sold
View as plain text  
Neat-o.

I think that's much better than the query I proposed with the  
subselect.  However, it doesn't give you price from the last sale of  
the product, instead it gives you highest price the product was sold  
for.  Also, it can give you multiple rows for each product_code if  
there are multiple sales at the same price.

Here is a small modification to Peter's query which will give you  
exactly one row for each product code showing the price at the last  
sale of that product.  (Assuming you have a synthetic key, perhaps an  
autoincrement field, called "id")

Also, an index on the product_code field will help the speed of this  
query a lot.  (I don't understand why the subselect query is still  
faster - I don't think it should be.)

SQL is rather fun.


> 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.date_sold < t2.date_sold OR (t1.date_sold=t2.date_sold AND  
> t1.id<t2.id)
> WHERE t2.product_code IS NULL
> ORDER BY t1.product_code;



Douglas Sims
Doug@stripped



On Sep 28, 2006, at 10:12 AM, Peter Brawley wrote:

> 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/mysql?unsub=1

Thread
Need to find last price and date product was soldmos28 Sep
  • Re: Need to find last price and date product was soldDouglas Sims28 Sep
  • Re: Need to find last price and date product was soldjoao28 Sep
    • Re: Need to find last price and date product was soldmos28 Sep
  • Re: Need to find last price and date product was soldJonathan Mangin28 Sep
  • Re: Need to find last price and date product was soldPeter Brawley28 Sep
    • Re: Need to find last price and date product was soldDouglas Sims28 Sep