GROUP BY without group functions in MySQL

Question

GROUP BY without group functions in MySQL

What information does MySQL give out if you do GROUP BY without group functions?

For example:

id|name|count|date
-------------------------
1 | aa |  1  |null
1 | bb |null |2007-12-12
1 |null|  2  |2015-10-12

GROUP BY id

Will there be any pattern in the output in the fields name, count and date or do random values from the sample fall there?

P.S. the same PostgreSQL will just throw an error.

3

mysql sql group-by

Author: cheops, 2015-10-22

Source

2 answers

score 2 · Answer 1

When executing a query, there is a certain order of processing strings. Set either by the developer or at the discretion of the optimizer. So, the string that was processed first in the group will be returned.

Next request:

SELECT *
FROM(
  SELECT *
  FROM(
              SELECT 1 id, 1 OrderBy, 10 value
    UNION ALL SELECT 1 id, 2 OrderBy, 20 value
    UNION ALL SELECT 1 id, 3 OrderBy, 30 value
  )T
  ORDER BY OrderBy
)T
GROUP BY id

It will return:

id OrderBy value
1  1       10

However, such a request:

SELECT *
FROM(
  SELECT *
  FROM(
              SELECT 1 id, 1 OrderBy, 10 value
    UNION ALL SELECT 1 id, 2 OrderBy, 20 value
    UNION ALL SELECT 1 id, 3 OrderBy, 30 value
  )T
  ORDER BY OrderBy DESC
)T
GROUP BY id

Returns already:

id OrderBy value
3  3       30

But, again, the optimizer can choose any sort, so in general, predict which row the optimizer will "choose". impossible.

That is, yes, theoretically, if you set a unique sort, you can use this feature of MySQL consciously. But at your own risk. Because this behavior is not described in the documentation and may change in future versions of the MySQL server.

score 1 · Answer 2

Response from comments

A random string will be returned, to be quite precise, the string that will be selected is certainly not random. However, due to the fact that as changes are made to the table, different rows will be displayed, for a "simple user" it will look exactly like a "randomly selected row".

This is not a typical behavior for a DBMS, perhaps only MySQL behaves like this. PostgreSQL, MS Sql, and Oracle will return an error because it is contradicts the SQL standard. In MySQL (if you do not set special parameters), in this case, an extended standard is used that allows you to do this. It is assumed that if the specified column is not listed in GROUP BY, then all its values within the group are the same and therefore it does not matter from which row it will be returned.