mysql order by 错误,MySQL错误的结果与GROUP BY和ORDER BY

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

Tuples:

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:

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?

解决方案

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;