From: Peter Brawley
Date: November 30 2008 4:40am
Subject: Re: How to find last price of items that were sold?
List-Archive: http://lists.mysql.com/mysql/215402
Message-Id: <4932193B.3070201@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------050905000504030000020200"

--------------050905000504030000020200
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >Is there a fast way to find the last price of an item that was sold, 
without doing a table join or subselect?

It can be fast but it needs a join, see "Within-group aggregates" at 
http://www.artfulsoftware.com/infotree/queries.php for examples.

PB

-----

mos wrote:
> Is there a fast way to find the last price of an item that was sold, 
> without doing a table join or subselect?
>
> The Details table has Product_Code: Char(20), Date_Sold: Date,  and 
> Price_Sold: Double. The products are sold for different prices during 
> the month and I need to retrieve the last price it was sold for. The 
> only thing I can think of would be to do a Group By to get the 
> max(Date_Sold) and then do a table join to the same table to get the 
> price.
>
> Example:
>
> create table tmp select Product_Code, max(Date_Sold) Date_Sold from 
> Details group by Product_Code;
> alter table tmp add index ix_Main (product_code, date_sold);
> select t1.*, t2.Price_Sold from tmp t1 left join Details t2 on 
> t1.product_code=t2.product_code and t1.date_sold=t2.date_sold;
>
> Is there a faster way to do it without doing a table join or creating 
> a temporary table?
>
> TIA
> Mike
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.176 / Virus Database: 270.9.11/1819 - Release Date: 11/29/2008 10:37 AM
>
>   

--------------050905000504030000020200--