MySQL issue with descending order

Question

My query :

$strSQL = "Select customers.*, count(ordersbase.OrderID) As Orders, ordersbase.OrderTime
        From customers
        Inner Join ordersbase On customers.ID = ordersbase.CustomerID
        Group By customers.ID
        Order By customers.ID, ordersbase.OrderTime Desc;";

Orders table:

I expected to get the OrderTime value as 20181008000000 (highest value in descending order) but it returns the other one:

    "0": {
        "Orders": 2,
        "OrderTime": "20181006000000"
    },

Answer 1

• Data is stored in an unordered fashion. Any non-aggregated column (which is not functionally dependent to columns in Group By) in the Select clause, and also not specified explicitly inside the GROUP BY clause, is non-deterministic. You need to use Max() function to get the latest order time. Do Read: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

• Order by is processed after grouping is already done. So, your usage of Order by does not work.

Try the following instead:

$strSQL = "Select customers.*, 
                  count(ordersbase.OrderID) As Orders, 
                  MAX(ordersbase.OrderTime) 
        From customers
        Inner Join ordersbase On customers.ID = ordersbase.CustomerID
        Group By customers.ID
        Order By customers.ID";