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