Geek asks and answers

Mysql Choose from Select

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 the result something like this (the snapshot only otherwise will return within 31 days):

order_date  cnt_order 
2012-08-29  580
2012-08-30  839
2012-08-31  1075

and my full request is selected based on the selection above:

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 this is the result:

order_date  highest_order
2012-08-01  1075

Which has the wrong date and always selects the first row of the date on which he suggested 2012-08-31. Perhaps this is a simple mistake that I do not know about. So, how to choose the right date in 2012-08-31? Any help would be great.

+5
source share
2 answers

You can try to streamline the sub-query result set; sort of:

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
+1
source

You can add ORDER BY order_date DESCto the subquery.

0
source

More articles:


All Articles