Hugo,

what about a descending index on Product_ID, DATE and fetch the first
row?

Or check your subquery against a SP. Anyway, a "right" index is
allways very important.

BTW, isn't DATE a reserved word?

Regards
Richard

--- In ib-support@y..., "Hug" <hugosan@r...> wrote:
> Let's suppose I have a SALES_DETAIL table with these records:
>
> Product_ID Date Price
> ACA13003 2001/09/14 145.00
> ACA13003 2001/09/23 155.00
> ACA13003 2001/10/06 152.00
>
> If I want to know the last date I sold this product, I ask
>
> SELECT MAX(Date)
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003'
>
> The result is 2001/10/06
>
> And, if I want to know the high price I sold this product, I ask
>
> SELECT MAX(Price)
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003'
>
> The result is 155.00
>
> But, what about the last price? I only think something like
>
> SELECT Price
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003'
> AND Date= (SELECT MAX(Date)
> FROM SALES_DETAIL
> WHERE Product_ID='ACA13003')
>
> to get 152.00 as the latest price this product is sold.
> But looking the PLAN, that query needs 2 access to the SALES_DETAIL
table,
> and it seems not optimized.
> Is there some better solution?
> Many thanks
> Hugo.