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.