>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
