mysql中选择,mysql从选择中选择

I have this query:

SELECT DATE( a.created_at ) AS order_date, count( * ) as cnt_order

FROM `sales_order_item` AS a

WHERE MONTH( a.created_at ) = MONTH( now())-1

GROUP BY order_date

which will return result something like this (snapshot only otherwise will return per 31 days):

order_date cnt_order

2012-08-29 580

2012-08-30 839

2012-08-31 1075

and my full query is selecting based on above selection:

SELECT order_date

, MAX(cnt_order) AS highest_order

FROM (

SELECT DATE (a.created_at) AS order_date

, count(*) AS cnt_order

FROM `sales_order_item` AS a

WHERE MONTH(a.created_at) = MONTH(now()) - 1

GROUP BY order_date

) AS tmax

But it result :

order_date highest_order

2012-08-01 1075

Which has the date wrong and always pick the first row of date where it suppose 2012-08-31. Maybe this is a simple error that I dont know. So how to get the date right point to 2012-08-31? Any help would be great.

解决方案

You could try ordering the sub query result set; something like:

SELECT

DATE (a.created_at) AS order_date,

COUNT(*) AS cnt_order

FROM

`sales_order_item` AS a

WHERE

MONTH(a.created_at) = MONTH(now()) - 1

GROUP BY

order_date

ORDER BY

cnt_order DESC