I am competing in a SQL database competition, and occasionally I come across problems like
SELECT name, MIN(a)
FROM table
According to the answer sheet, the outputted name is indeterminate. However, in a later practice test I am asked to find the exact output of such a query, implying there is a way to determine the returned name value.
The answer keys have a tendency of sometimes being mistaken (too often for comfort). So I ran a query like that one in the W3Schools try-it-yourself box, and got an error. However, I tried it in Codecademy, and it returned the name value associated with the minimal "a" value.
There's so much contradicting information that I don't know what to think!
As a general rule: if you do an aggregation in your
SELECT
clause, and some columns are not aggregated, they should be listed in aGROUP BY
clause.As an example, this is what is described for Postgres, with my highlight in bold:
MySQL also has a neat help page on the topic for a reason that I will detail later.
As written, MySQL accepts queries where columns are omitted from the
GROUP BY
clause, including the case where all the columns are omitted (= the entire clause is omitted) but this is an exception to the standard.If the
ONLY_FULL_GROUP_BY
option is off, then it will allow such a query to be executed but the value you will get for the non aggregated columnname
will be the first that the database encounters when reading the table.To my knowledge, MySQL is the only DBMS allowing such queries. I think most people will agree you should never omit non-aggregated columns even when the DBMS allows it, because it is non-standard.
To be complete and if you read both quotes carefully, you must have noticed the standard actually allows non-aggregated columns to be omitted in 1 case: if they are functionally dependent with the aggregated columns.
My conclusion is that you managed to make your query work on an instance of MySQL (Codecademy) and it failed elsewhere.