| Subject | Re: [firebird-support] SQL - Most recent order for each product |
|---|---|
| Author | Robert martin |
| Post date | 2004-09-29T01:22:01Z |
Hi
I would like to precede the following with the following two points... 1. I am not an SQL guru, 2. I don't pretend to understand your data structures.
I think you need a sub select in your where clause. The problem with your sql is that you are returning the order with the Max date AND the order with the MAX price, these are not necessarily the same orders.
I think you need to change your sql so it works in two steps,
A - Find the most recent order with the item in it
B - Find the Max price of that item in THAT order.
So something like
SELECT I.PRODUCT_ID, O.ORDER_DATE, MAX(I.PRICE)
FROM ORDERS O
JOIN ITEMS I
ON O.ORDER_ID = I.ORDER_ID
JOIN PRODUCTS P ON
I.PRODUCT_ID = P.PRODUCT_ID
WHERE
CUSTOMER_NUMBER = 1000
AND O.ORDER_ID = (select Max(ORDER_ID) from Orders Join Items on Items.Order_Id = Orders.Order-ID where Items.Product_ID = I.Product_id)
GROUP BY
I.PRODUCT_ID,
O.ORDER_DATE,
I.PRICE
I haven't tested this SQL (and it may be totally off track - I am no expert) but it is a reasonable idea I think. ps it assumes that the order with the max Order_id will be the most recent. If this isn't the case you will need to rework it.
p.s. Orders is not a good name for a table, I should know we have a table called ClientOrders !
Rob Martin
Software Engineer
phone 03 377 0495
fax 03 377 0496
web www.chreos.com
Wild Software Ltd
I would like to precede the following with the following two points... 1. I am not an SQL guru, 2. I don't pretend to understand your data structures.
I think you need a sub select in your where clause. The problem with your sql is that you are returning the order with the Max date AND the order with the MAX price, these are not necessarily the same orders.
I think you need to change your sql so it works in two steps,
A - Find the most recent order with the item in it
B - Find the Max price of that item in THAT order.
So something like
SELECT I.PRODUCT_ID, O.ORDER_DATE, MAX(I.PRICE)
FROM ORDERS O
JOIN ITEMS I
ON O.ORDER_ID = I.ORDER_ID
JOIN PRODUCTS P ON
I.PRODUCT_ID = P.PRODUCT_ID
WHERE
CUSTOMER_NUMBER = 1000
AND O.ORDER_ID = (select Max(ORDER_ID) from Orders Join Items on Items.Order_Id = Orders.Order-ID where Items.Product_ID = I.Product_id)
GROUP BY
I.PRODUCT_ID,
O.ORDER_DATE,
I.PRICE
I haven't tested this SQL (and it may be totally off track - I am no expert) but it is a reasonable idea I think. ps it assumes that the order with the max Order_id will be the most recent. If this isn't the case you will need to rework it.
p.s. Orders is not a good name for a table, I should know we have a table called ClientOrders !
Rob Martin
Software Engineer
phone 03 377 0495
fax 03 377 0496
web www.chreos.com
Wild Software Ltd
----- Original Message -----
From: Martin Clarke
To: firebird-support@yahoogroups.com
Sent: Wednesday, September 29, 2004 12:03 PM
Subject: RE: [firebird-support] SQL - Most recent order for each product
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]
Yahoo! Groups Sponsor
ADVERTISEMENT
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]