GROUP BY on MySQL

Question

I have a question.

When I run this code on this dataset:

SELECT a.customerNumber, a.checkNumber 
FROM (SELECT customerNumber,
             checkNumber, min(paymentDate) as paymentDate 
      FROM payments 
      GROUP BYcustomerNumber) AS a;

I was wondering when I do not specify other columns that are not used in group by function. MySQL allows me to do that, but I rememberer that without specifying other columns, I would get this message, You tried to execute a query that does not include the specified expression 'one of column' as part of an aggregate function.

Am I missing some key points here? I think MySQL will automatically return the first entry of unspecified column without specifying this column in an aggregate function.

Side note: GROUP BY is not a function, it is a clause. — GMB
– GMB, Commented Dec 5, 2019 at 16:43

Bill Karwin · Accepted Answer · 2019-12-05 16:42:24Z

1

It depends whether server option sql_mode=ONLY_FULL_GROUP_BY is enabled or not. The option is enabled by default since version 5.7.

The documentation goes into great details about the behavior of the server when the option is disabled. In a nutshell:

the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

Bottom line: always list all non-aggregated columns in the group by clause, regardless of the setting of your server; this is a best practice and the SQL standard, and the only way to get predictable results.

edited Dec 5, 2019 at 16:42

Bill Karwin

567k87 gold badges707 silver badges867 bronze badges

answered Dec 5, 2019 at 16:39

GMB

224k25 gold badges102 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

GROUP BY on MySQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related