July 26, 2005 at 2:33 pm
Hello Everyone!
I've searched here and found several examples that are similar to my situation. The difference I believe is that in the examples all the data is in one table. Using the Northwind database Here's my sql:
select p.ProductID, o.orderID, o.OrderDate
from Products p
join [Order Details] od on p.ProductID = od.ProductID
join Orders o on o.OrderID = od.OrderID
join (select o1.OrderID, max(o1.OrderDate) as MaxOrder
from orders o1 group by o1.Orderid) as o2
on o.orderid = o2.orderid
and o.orderdate = MaxOrder
where CategoryID = 1
I'm getting 404 rows, I'm expecting 12.
Can anyone tell me what I'm doing wrong?
Thanks!
July 26, 2005 at 3:00 pm
What question are you asking of the database when you wrote your query?
It appears to me your asking for all product id's for all orders and the order date where categoryid = 1.
and the
join (select o1.OrderID, max(o1.OrderDate) as MaxOrder
from orders o1 group by o1.Orderid) as o2
on o.orderid = o2.orderid
and o.orderdate = MaxOrder
has no effect on the results of your query.
July 26, 2005 at 3:34 pm
Thanks for your quick reply!
I'm trying to get the last order (max(orderdate) for each item. Ultimately, I'm building a separate association table between products and orders using only the latest order. We are doing a conversion and the structure of my actual tables is the same as in the Northwind. In my case the last 'order' is current and all the others are actually historical and can be ignored (at least for this association). I'm selecting a category here to simulate the criteria for the 'products' subset that I need.
Gary
July 27, 2005 at 10:31 am
how about:
select productname, categoryid, max(od.orderid) as lastOrderID, max(orderdate) as lastOrderDate
from products p
left join [order details] od on od.productid = p.productid
left join orders o on od.orderid = o.orderid
where categoryid = 1
group by productname, categoryid
Chai |
1998-05-05 00:00:00.000
Chang
1998-05-06 00:00:00.000
Chartreuse verte
1998-05-06 00:00:00.000
Côte de Blaye
1998-04-17 00:00:00.000
Guaraná Fantástica
1998-05-05 00:00:00.000
Ipoh Coffee
1998-05-04 00:00:00.000
Lakkalikööri
1998-05-06 00:00:00.000
Laughing Lumberjack Lager
1998-04-28 00:00:00.000
Outback Lager
1998-04-30 00:00:00.000
Rhönbräu Klosterbier
1998-05-06 00:00:00.000
Sasquatch Ale
1998-05-01 00:00:00.000
Steeleye Stout
1998-04-23 00:00:00.000
July 27, 2005 at 11:28 am
Peter,
Thank-you for your solution. I really thought you had it.
Unfortunately, it only works because in Northwind all the orderID's are in the same sequence as the orderdate. So, having max on both gets you the 'correct' result. If you change the orderDate on 11070 to 04/23/98, then the result for item 1 is order 11070, (the max orderID containing item 1) and 04/24/98 (the max orderDate which happens to be on order 11047). This sadly, is the scenario that my data is in; my 'orderId' is assigned randomly instead of sequentially when compared with my 'OrderDate.
Gary
July 27, 2005 at 3:15 pm
select p.productid, o.orderid, orderdate
from products p
inner join [order details] od on od.productid = p.productid
inner join orders o on od.orderid = o.orderid
inner join (
select productid, max(orderdate) as maxOrderDate
from [order details] od
inner join orders o on od.orderid = o.orderid
group by productid) as p1
on orderdate = p1.maxOrderDate and p.productid = p1.productid
where categoryid = 1
I hope this isn't a dup post.
This returns two orderid values if a product has two orders on the date that is the last date the product was ordered. A more accurate timestamp might prevent this.
Peter.
July 28, 2005 at 7:06 am
Peter,
Yes! Thank-you, Thank-you, Thank-you!
I can see now where I went wrong not joining the Order Details and grouping by productID.
Fortunately, this particular script doesn't suffer from the duplicates issue, although I think the next one does - but I'll cross that bridge when I get to it.
Thanks again for your time and effort.
May God Richly Bless you
Gary
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply