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"
    },

it returns one because you are using Group By customers.ID you can use aggregate function. — Nanhe Kumar, Oct 10 '18 at 13:40
possible duplicate https://stackoverflow.com/questions/10030787/using-order-by-and-group-by-together — Siva, Oct 10 '18 at 13:41
@NanheKumar, sir, i am not looking for two result, i just looking for correct value, as it returning the wrong date :( for descending order :( — Zakir_SZH, Oct 10 '18 at 13:42

Madhur Bhaiya · Accepted Answer · 2018-10-10T13:47:42.370

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";

MySQL issue with descending order

1 Answers1