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.