I guess I know that part isn't right, I'm just don't know what is.
This seems easy enough. I hope someone knows.


--- In firebird-support@yahoogroups.com, "Martin Clarke" <martin@d...>
wrote:

> 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@y...]
> 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]