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