I have a list of dated orders, each with a list of items that have
been ordered and their prices. For a given customer, I want to show a
list of products that the customer has ordered with the most recent
order date and price.

I'm having trouble getting one entry per product. Note, if two orders
for the same product happened on the same day, I want the one with the
higher price.

I'm having trouble getting just one entry per product. Here's what
I've tried:

SELECT I.PRODUCT_ID, O.ORDER_DATE, I.PRICE

FROM ORDERS O

LEFT OUTER JOIN ITEMS I
ON O.ORDER_ID = I.ORDER_ID

LEFT OUTER JOIN PRODUCTS P ON
I.PRODUCT_ID = P.PRODUCT_ID

WHERE
CUSTOMER_NUMBER = 1000

GROUP BY
I.PRODUCT_ID,
O.ORDER_DATE,
I.PRICE

HAVING
I.PRICE = MAX(I.PRICE)
AND
I.ORDER_DATE = MAX(ORDER_DATE)

ORDER BY TK.TICKDATE DESC,I.PRICE DESC

What am I missing? Shouldn't the having clause be giving me one enrty
per product ID?