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

Here's the most recent SQL:

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


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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