SELECT DISTINCT and ORDER BY in MySQL

November 19, 2018, at 5:10 PM

It seems like in version 5.7 of MySQL, they added one nasty thing which was (or still is) a real headache for those who deal with SQL Server.

The thing is: MySQL throws an error, when you try to SELECT DISTINCT rows for one set of columns and want to ORDER BY another set of columns. Previously, in version 5.6 and even in some builds of version 5.7 you could do this, but now it is prohibited (at least by default).

I hope there exists some configuration, some variable that we could set to make it work. But unfortunately I do not know that nasty variable. I hope someone knows that.

EDIT

This is some typical query in my case that worked literally for years (until the last build of MySQL 5.7):

SELECT DISTINCT a.attr_one, a.attr_two, a.attr_three, b.attr_four FROM table_one a
LEFT JOIN table_two b ON b.some_idx = a.idx
ORDER BY b.id_order

And, indeed, if I now include b.id_order to the SELECT part (as MySQL suggests doing), then what I will get, will be rubbish.

Answer 1

In most cases, a DISTINCT clause can be considered as a special case of GROUP BY. For example,

ONLY_FULL_GROUP_BY

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior )

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

for more http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

for particular answer

SELECT DISTINCT attr_one,
            attr_two,
            attr_three,
            attr_four
FROM
  (SELECT a.attr_one,
      a.attr_two,
      a.attr_three,
      b.attr_four
   FROM table_one a
   LEFT JOIN table_two b ON b.some_idx = a.idx
   ORDER BY b.id_order) tmp

Answer 2

I have read the post on the link you mentioned, and looks like been given the clear explanation of why the error is thrown and how to avoid it.

In your case you may want to try the following (not tested of course):

SELECT a.attr_one, a.attr_two, a.attr_three, b.attr_four 
FROM table_one a
LEFT JOIN table_two b ON b.some_idx = a.idx
GROUP BY a.attr_one, a.attr_two, a.attr_three, b.attr_four 
ORDER BY max(b.id_order)

You should choose whether to use ORDER BY max(b.id_order), or ORDER BY min(b.id_order) or other aggregate function