I belive that the MAX below asks for the MAX of PRICE inside unique
combinations of PRODUCT_ID and ORDER_DATE, but it doesn't give just
the most recent and it doesn't give just one entry per product.

In any case, I appreciate the comments. This one's a bit tricky.


--- In firebird-support@yahoogroups.com, "radevojvodic" <vrade@i...>
wrote:

> Hi,
>
> Would select like this help
>
> SELECT
> DISTINCT
> P.PRODUCT_ID,
> O.ORDER_DATE,
> MAX(ORDER_ITEM.PRICE)
> Group By P.PRODUCT_ID, O.ORDER_DATE,
> ...
>
>
> --- In firebird-support@yahoogroups.com, "ra8009" <ra8009@y...>
> wrote:
> > Yes, you're right. From what I show here it's not needed. I
> actually
> > have the "real" query in descending order so I get the most recent
> > items at the top.
> >
> > Now I just need to get rid of everything but the most recent/
> highest
> > price. Do you know why the MAX in the HAVING doesn't accomplish
> that
> > ot what would work?
> >
> > --- In firebird-support@yahoogroups.com, "Alan McDonald"
> <alan@m...>
> > wrote:
> > > > SELECT
> > > > DISTINCT
> > > > P.PRODUCT_ID,
> > > > O.ORDER_DATE,
> > > > ORDER_ITEM.PRICE
> > > >
> > > > FROM PRODUCTS P
> > > >
> > > > JOIN ORDER_ITEMS I
> > > > ON P.PRODUCT_ID = I.PRODUCT_ID
> > > >
> > > > JOIN ORDERS O
> > > > ON I.ORDER_ID = O.ORDER_ID
> > > >
> > > > JOIN CUSTOMER C
> > > > ON C.CUSTOMER_ID = O.CUSTOMER_ID
> > > >
> > > > WHERE
> > > > C.CUSTOMER_ID = 10300
> > > >
> > > > GROUP BY P.PRODUCT_ID, O.ORDER_DATE, I.PRICE
> > > > HAVING ORDER_DATE = MAX(ORDER_DATE)
> > > > ORDER BY 1,2
> > >
> > > correct me if I'm wrong but the group by clause will order by -
> by
> > default.
> > > there's no need for the order by clause when you are grouping by
> > > Alan