List:General Discussion« Previous MessageNext Message »
From:mos Date:November 29 2008 9:38pm
Subject:How to find last price of items that were sold?
View as plain text  
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

Thread
How to find last price of items that were sold?mos29 Nov
  • Re: How to find last price of items that were sold?Gautam Gopalakrishnan29 Nov
    • Re: How to find last price of items that were sold?mos30 Nov
  • Re: How to find last price of items that were sold?Peter Brawley30 Nov