CODEWITHSUNDEEP
mysqlgroup-by
Telecom
Telecom

Reputation: 3

GROUP BY on MySQL

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.

Upvotes: 0

Views: 54

Answers (1)

GMB
GMB

Reputation: 222462

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.

Upvotes: 1

Related Questions