Misunderstandings with sql_mode=ONLY_FULL_GROUP_BY

Question

PHP

The muscle has an ONLY_FULL_GROUP_BY mode, which forces the server to understand only those queries with GROUP BY, in which all non-aggregating fields specified in SELECT according to the SQL standard are specified for grouping. And okay, when I have a query for a link of tables, where I can place the names of the columns with a functional dependence on the keys in GROUP BY. Put GROUP BY 1,2,3,4,5,6..X there and forgot. And what to do with queries where I have to group by the field of just one table?

So there is a `dates` table, bye.

id   name            date
1    Жанна Фриске    2013-12-12
2    Жанна Фриске    2013-12-13
3    Вера Брежнева   2013-12-14

Now we pedal two identical requests. One with empty sql_mode, the other with ONLY_FULL_GROUP_BY:

SET @@sql_mode = "";
SELECT * FROM dates GROUP BY name;

SET @@sql_mode = "ONLY_FULL_GROUP_BY";
SELECT * FROM dates GROUP BY name;

In the first case, the expected result (it doesn't matter to me that the entry with id=1, not 2, was selected for Zhanna):

id   name            date
1    Жанна Фриске    2013-12-12
3    Вера Брежнева   2013-12-14

And the second one is wrong. SQL error 1055 dates.id is not in GROUP BY. Which is logical, because id is implicitly present in the SELECT. Ok, I will add to GROUP BY all the column names that are in SELECT. But then in the response the server will return all the rows from the table to me, which is also logical, but I don’t need it, since the grouping by name never happened. And the order of the field names does not matter here. This is where I have a question for the public. What should I do in the case of such a simple grouping when selecting from one table? How to create a query on a selection of data grouped by name from one table so that it works with different sql_mode?

Answer 1

If you just need all the names without repetitions, then SELECT DISTINCT name FROM dates
If you also need a date, then you need to explicitly indicate which one. for exampleSELECT MAX(date), name FROM dates GROUP BY name