List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:September 28 2006 4:53am
Subject:Re: Need to find last price and date product was sold
View as plain text  
You could do something like that by using a derived table (subselect)  
to pick the max date for each product sale and then joining that on  
the products table again to pick up the amounts.

Note that you must use mysql 5.x to be able to use subselects.

Also this will return multiple rows for a given account if there are  
more than one sale on the last day each item has been sold.  (There  
are several ways around that if it's a problem).

I just ran such a query on the table I'm working on right now to test  
the idea... there are about 3000 rows in this table so the 0.52  
second query time seems high, but there's only a primary key index.   
I think indexing on the transaction date (date_xact) would probably  
help in this example...


mysql> 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;
+---------+------------+-----------+
| account | maxdate    | amount    |
+---------+------------+-----------+
| 0       | 2005-08-17 | -15.06    |
| 1       | 2006-07-24 | 26790.00  |
| 2       | 2006-07-14 | 1500.00   |
| 2       | 2006-07-14 | 2040.00   |
| 2       | 2006-07-14 | 2520.00   |
| 3       | 2006-07-14 | -193.98   |
| 3       | 2006-07-14 | -328.98   |
| 3       | 2006-07-14 | -418.21   |
| 4       | 2006-07-14 | -186.00   |
| 4       | 2006-07-14 | -252.96   |
| 4       | 2006-07-14 | -312.48   |
| 5       | 2006-07-14 | -43.50    |
| 5       | 2006-07-14 | -59.16    |
| 5       | 2006-07-14 | -73.08    |
| 9       | 2006-06-27 | 60.06     |
| 9       | 2006-06-27 | 196.77    |
| 10      | 2006-03-27 | 60.04     |
| 11      | 2006-04-13 | 65.00     |
| 12      | 2006-06-23 | -272.03   |
| 13      | 2006-02-16 | 100.00    |
| 14      | 2006-07-14 | 114.75    |
| 14      | 2006-07-14 | 156.06    |
| 14      | 2006-07-14 | 192.78    |
| 15      | 2006-07-24 | -18240.00 |
| 15      | 2006-07-24 | -8550.00  |
| 16      | 2006-07-11 | -800.00   |
| 17      | 2004-07-07 | -51.87    |
| 17      | 2004-07-07 | -50.49    |
| 17      | 2004-07-07 | -27.31    |
| 18      | 2006-06-01 | 288.77    |
| 19      | 2006-05-11 | 175.00    |
| 20      | 2006-01-05 | 50.00     |
| 21      | 2006-07-15 | 152.90    |
| 22      | 2006-07-19 | -600.00   |
| 23      | 2006-05-31 | 10.00     |
| 24      | 2005-07-29 | -277.83   |
| 25      | 2005-11-08 | -178.00   |
| 26      | 2006-03-24 | 94.24     |
| 26      | 2006-03-24 | 74.40     |
| 26      | 2006-03-24 | 248.00    |
| 27      | 2006-03-24 | 22.04     |
| 27      | 2006-03-24 | 17.40     |
| 27      | 2006-03-24 | 58.00     |
| 28      | 2006-06-07 | 185.00    |
| 29      | 2006-03-27 | 136.00    |
| 30      | 2006-07-18 | 398.16    |
| 31      | 2006-02-04 | 500.00    |
| 32      | 2006-04-06 | 64.00     |
| 35      | 2006-04-15 | 1000.00   |
| 37      | 2005-12-23 | 200.00    |
| 38      | 2006-05-12 | -51.04    |
| 39      | 2005-07-31 | 1191.00   |
| 40      | 2006-05-29 | 65.00     |
+---------+------------+-----------+
53 rows in set (0.52 sec)


Douglas Sims
Doug@stripped



On Sep 27, 2006, at 11:36 PM, 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
>
> -- 
> 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