List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:September 28 2006 1:27pm
Subject:Re: Need to find last price and date product was sold
View as plain text  
Section 3.6.2 of the 4.1 manual has this example
using a subselect:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

I use this basic syntax with max(date) alot.



----- Original Message ----- 
From: "João Cândido de Souza Neto" <joao@stripped>
To: <mysql@stripped>
Sent: Thursday, September 28, 2006 8:39 AM
Subject: Re: Need to find last price and date product was sold


> Please, try to do the follow select, i think it´ll works fine.
>
> select product_code, max(date_sold), price_sold from trans group by
> product_code order by product_code
>
>
>
> "mos" <mos99@stripped> escreveu na mensagem
> news:6.0.0.22.2.20060927230926.0353a8f8@ style="color:#666">stripped...
> > 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