--- 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