mysql left join group by,MySQL LEFT JOIN，GROUP BY和ORDER BY无法按要求工作

I have a table

'products' => ('product_id', 'name', 'description')

and a table

'product_price' => ('product_price_id', 'product_id', 'price', 'date_updated')

I want to perform a query something like

SELECT `p`.*, `pp`.`price`

FROM `products` `p`

LEFT JOIN `product_price` `pp` ON `pp`.`product_id` = `p`.`product_id`

GROUP BY `p`.`product_id`

ORDER BY `pp`.`date_updated` DESC

As you can probably guess the price changes often and I need to pull out the latest one. The trouble is I cannot work out how to order the LEFT JOINed table. I tried using some of the GROUP BY functions like MAX() but that would only pull out the column not the row.

Thanks.

解决方案

It appears that it is impossible to use an ORDER BY on a GROUP BY summarisation. My fundamental logic is flawed. I will need to run the following subquery.

SELECT `p`.*, `pp`.`price` FROM `products` `p`

LEFT JOIN (

SELECT `price` FROM `product_price` ORDER BY `date_updated` DESC

) `pp`

ON `p`.`product_id` = `pp`.`product_id`

GROUP BY `p`.`product_id`;

This will take a performance hit but as it is the same subquery for each row it shouldn't be too bad.