select core_subscription.id,core_order_product.* FROM core_order_product
inner join
(
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
)
group_order_product ON  core_order_product.order_create_date = group_order_product.create_date and core_order_product.company_id = group_order_product.company_id  inner join core_subscription on core_subscription.id = group_order_product.subscription_id where core_order_product.company_id = 1032;

returning to many rows. because of that order_id