--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> --- In firebird-support@yahoogroups.com, "ra8009" wrote:
> > The tables and fields I need are:
> >
> > PRODUCTS (PRODUCT_ID) - just one per product ordered
> > ORDERS (ORDER DATE) - just the most recent
> > ORDER_ITEMS (PRICE) - higher price used to break ORDER_DATE "ties"
> >
> > The only problem I have is that I'm getting all records, not just
> > the latest/highest price one. The MAX in the HAVING clause seems to
> > have no effect???
>
> I disagree, the main problem here (I haven't read all the former
> correspondence, but this description is enough to understand) is that
> whomever wrote the SQL either doesn't have a clear understanding of
> your requirements or expects the computer to understand what you want
> rather than what you ask for. I'll start by writing plain english:
>
> You want one record per product_id. Then either select distinct or
> group by product_id, in your case you want group by.
>
> You want the latest date, then select max(order_date).
>
> You want the highest price. Normally that would have been max(price),
> but in your case you don't want the highest ever price for the
> product, just the highest price for the product on a given date -
> hence you need a (somewhat complicated) subselect.
>
> Here's an attempt to write the SQL you then need:
>
> SELECT
> P.PRODUCT_ID,
> MAX(O.ORDER_DATE),
> (SELECT MAX(I2.PRICE) //Find max price
> FROM ORDER_ITEMS I2
> JOIN ORDERS O2
> ON I2.ORDER_ID = O2.ORDER_ID
> JOIN CUSTOMER C2
> ON C2.CUSTOMER_ID = O2.CUSTOMER_ID
> WHERE P2.PRODUCT_ID = P.PRODUCT_ID //for the same product
> AND C2.CUSTOMER_ID = C.CUSTOMER_ID //and customer
> AND NOT EXISTS(SELECT * FROM PRODUCTS P3 //without a later date
> JOIN ORDER_ITEMS I3
> ON P3.PRODUCT_ID = I3.PRODUCT_ID
> JOIN ORDERS O3
> ON I3.ORDER_ID = O3.ORDER_ID
> JOIN CUSTOMER C3
> ON C3.CUSTOMER_ID = O3.CUSTOMER_ID
> WHERE C3.CUSTOMER_ID = 10300
> AND P3.PRODUCT_ID = P2.PRODUCT_ID
> AND O3.ORDER_DATE > O2.ORDER_DATE)) AS MAXPRICE
> 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
>
> I got quite confused while writing this, so it may not be 100%
> correct. It may also be possible to simplify a bit, but you'll have to
> examine for yourself whether that is possible. I think all this
> probably could have been written much simpler in a stored procedure.
>
> HTH,
> Set

This looks good to me, but I'm having trouble getting it to run. The
complaint is "Invalid expression in the select list(not contained in
either an aggregate function or GROUP BY clause)"

Usually I can easily fix this type of error. Here I'm having trouble.
What do I need in the GROUP BY to make it work?