Sorry, dont have a solution but your code will only work if the max price and the max order date are on the same record, which is probably unlikely:
HAVING
I.PRICE = MAX(I.PRICE)
AND
I.ORDER_DATE = MAX(ORDER_DATE)



-----Original Message-----
From: ra8009 [mailto:ra8009@...]
Sent: Wednesday, 29 September 2004 10:00 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] SQL - Most recent order for each product


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?



Yahoo! Groups Sponsor

ADVERTISEMENT
<http://us.ard.yahoo.com/SIG=129522df7/M=295196.4901138.6071305.3001176/D=groups/S=1705115386:HM/EXP=1096502393/A=2128215/R=0/SIG=10se96mf6/*http://companion.yahoo.com> click here
<http://us.adserver.yahoo.com/l?M=295196.4901138.6071305.3001176/D=groups/S=:HM/A=2128215/rand=109451243>


_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .



_____

Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au






[Non-text portions of this message have been removed]