Subject Re: [ib-support] Re: The latest price of a product
Author Ann W. Harrison
>--- 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
> > WHERE Product_ID='ACA13003'
> > AND Date= (SELECT MAX(Date)
> > 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

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.


We have answers.