Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return OrderId of row with Max OrderDate per ProductID Expand / Collapse
Author
Message
Posted Tuesday, July 26, 2005 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:11 PM
Points: 4, Visits: 2

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!

 

Post #204619
Posted Tuesday, July 26, 2005 3:00 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, October 24, 2014 9:55 AM
Points: 1,485, Visits: 1,036

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.

 

Post #204631
Posted Tuesday, July 26, 2005 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:11 PM
Points: 4, Visits: 2

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

Post #204643
Posted Wednesday, July 27, 2005 10:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 6:57 AM
Points: 26, Visits: 1

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

 

Post #205000
Posted Wednesday, July 27, 2005 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:11 PM
Points: 4, Visits: 2

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

 

 

Post #205027
Posted Wednesday, July 27, 2005 3:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 6:57 AM
Points: 26, Visits: 1

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.

Post #205107
Posted Thursday, July 28, 2005 7:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 12:11 PM
Points: 4, Visits: 2

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

 

Post #205260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse