>--- In ib-support@y..., "Hug" <hugosan@r...> wrote:
> >
> > If I want to know the last date I sold this product ...
> >
> > And, if I want to know the high price I sold this product ...
> >
> > 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')
> >
> > But looking the PLAN, that query needs 2 access to the
> > SALES_DETAIL table,and it seems not optimized.

I think that what you're doing is OK, and if you have a
descending index on your date field, it should be reasonably
efficient. Depending on how you store your dates, you may
get more than one price.

At 01:55 PM 10/15/2001 +0000, Richard Saeger wrote:

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

That won't work because the concept of "first row" is not well
defined unless you ask for an ordered result - something which
could take longer than the sub-select above.


Regards,

Ann
www.ibphoenix.com
We have answers.