MySQL wrong results with GROUP BY and ORDER BY

Question

I have a table user_comission_configuration_history and I need to select the last Comissions configuration from a user_id.

Tuples:

enter image description here

I'm trying with many queries, but, the results are wrong. My last SQL:

SELECT *
    FROM(
        SELECT * FROM user_comission_configuration_history
        ORDER BY on_date DESC
    ) AS ordered_history
WHERE user_id = 408002
GROUP BY comission_id

The result of above query is:

enter image description here

But, the correct result is:

id    user_id    comission_id    value         type          on_date
24    408002     12              0,01          PERCENTUAL    2014-07-23 10:45:42
23    408002     4               0,03          CURRENCY      2014-07-23 10:45:41
21    408002     6               0,015         PERCENTUAL    2014-07-23 10:45:18

What is wrong in my SQL?

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Marcus Adams, Jul 23 '14 at 14:40
Yes, the response of Bill Karwin is correct and better performatic. On the duplicate suggestion. — Maykonn, Jul 23 '14 at 15:19

Gordon Linoff · Accepted Answer · 2014-07-23T19:48:32.200

This is your query:

SELECT *
FROM (SELECT *
      FROM user_comission_configuration_history
      ORDER BY on_date DESC
     ) AS ordered_history
WHERE user_id = 408002
GROUP BY comission_id;

One major problem with your query is that it uses a MySQL extension to group by that MySQL explicitly warns against. The extension is the use of other columns in the in theselect that are not in the group by or in aggregation functions. The warning (here) is:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

So, the values returned in the columns are indeterminate.

Here is a pretty efficient way to get what you want (with "comission" spelled correctly in English):

SELECT *
FROM user_commission_configuration_history cch
WHERE NOT EXISTS (select 1
                  from user_commission_configuration_history cch2
                  where cch2.user_id = cch.user_id and
                        cch2.commission_id = cch.commission_id and
                        cch2.on_date > cch.on_date
                 ) AND
      cch.user_id = 408002;

Gordon Linoff, a very good response. But the results are still wrong. And change the second WHERE on cch.user_id to AND. Your SQL return 2 tuples and the correct number of tuples to user_id 408002 is 3. — Maykonn, Jul 23 '14 at 14:54
The reason for 2 tuples instead of 3 I believe is due to the join on cch2.type. I think it should be on comission_ID given desired results. However the root cause of your problem being the indeterminate nature of the extended group by is dead on. — xQbert, Jul 23 '14 at 15:17
@Maykonn . . . The answer should now be correct on both fronts. — Gordon Linoff, Jul 23 '14 at 19:49

score 1 · Answer 2 · answered Jul 23 '14 at 14:36

Here's one way to do what your trying. It gets the max date for each user_ID and commissionID and then joins this back to the base table to limit the results to just the max date for each commissionID.

SELECT *
FROM user_comission_configuration_history A
INNER JOIN (
        SELECT User_ID, Comission_Id, max(on_Date) mOn_Date
        FROM user_comission_configuration_history
        Group by User-Id, Comission_Id
    ) B
 on B.User_ID = A.User_Id
and B.Comission_Id = A.Comission_ID
and B.mOnDate=A.on_date
WHERE user_id = 408002
ORDER BY on_Date desc;

MySQL wrong results with GROUP BY and ORDER BY

2 Answers2

Linked