select core_order_product.order_id,product_name, MAX(order_create_date) as create_date,subscription_id from core_order_product inner join  core_subscription_order on  core_order_product.order_id = core_subscription_order.order_id group by product_name,subscription_id,core_order_product.order_id order by subscription_id

if i take out the order_id i get the result I want. but i need that order id. :)