At 11:59 PM 28/09/2004 +0000, you wrote:

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

There's a muddle here. You seem to have order dates on both the order
header and on the items.

Your query isn't about the order header at all, but about the product info
for the items that have been ordered, when they were ordered and, if the
same customer had more than one order on the same day, which item had the
highest price.

There is ambiguity in your requirement as stated, since you have order
dates on both the headers and the items. Not only have you have reinforced
the ambiguity in the search condition in the HAVING clause....

I.ORDER_DATE = MAX(ORDER_DATE)

but you are trying to create a HAVING condition on an aggregation that
doesn't exist in the column specification.

Be clear about how you want these dates to interact and design the query
with that in mind.

I fail to see why you're using outer joins here. Do you have orders that
have no items? And, if you did, would those order headers be relevant to a
query that wants to know about items? Similarly, do you have items that are
not products?

You can be helped with this if you provide an output spec that is
consistent with the way you seem to be storing the data.

./heLen