What information gives MYSQL 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

There will be some regularity in the issuance in the fields name , count and date or there are random values ​​from the sample?

p.s. The same postgreSQL just gives an error.

Answer 1, Authority 100%

When the query is executed there is a certain order of row processing.
Specified either by the developer or at the discretion of the optimizer.
So it will be issued a string that was processed first in the group.

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

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

will return:

ID OrderBy Value
3 3 30.

But, I repeat, the sorting can choose an optimizer anything, therefore, in general, it is impossible to predict which string “select” the optimizer.

i.e. Yes, theoretically, if you specify a unique sorting, you can use this feature of MySQL consciously. But at your own risk and risk. Since this behavior is not described in the documentation and may change in future versions of MySQL Servers.

Answer 2, Authority 50%

Reply from comments

A random string will be issued if it is completely accurate, then the string that will be selected, of course, is not random. However, due to the fact that as changes to the table change, different lines will be issued, then for a “simple user” it will look exactly like “Randomly selected string.”

This is not a typical behavior for the DBMS, it is probably only MySQL. PostgreSQL, MS SQL and Oracle will issue an error because it contradicts the SQL standard. In MySQL, (if not specifying special parameters) in this case, a certain advanced standard is used that allows you to do so. It is believed that if the specified column is not listed in the group by , then all its values ​​within the group are the same and therefore it is not important from which line it will be returned.