Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return OrderId of row with Max OrderDate per ProductID


Return OrderId of row with Max OrderDate per ProductID

Author
Message
Warnersoft, Inc.
Warnersoft, Inc.
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!


Ray M
Ray M
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1491 Visits: 1076

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.


Warnersoft, Inc.
Warnersoft, Inc.
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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


Peter Laube
Peter Laube
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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


Warnersoft, Inc.
Warnersoft, Inc.
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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


Peter Laube
Peter Laube
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
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.


Warnersoft, Inc.
Warnersoft, Inc.
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search