Databases
StackOverflow2013
Postsdbo
POSQL vs MySQL: Rules about aggregate operations and GROUP BY
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL vs MySQL: Rules about aggregate operations and GROUP BY
    text
    copied!<p>In <a href="http://rads.stackoverflow.com/amzn/click/0072465638" rel="nofollow noreferrer">this book</a> I'm currently reading while following a course on databases, the following example of an illegal query using an aggregate operator is given:</p> <blockquote> <p><em>Find the name and age of the oldest sailor.</em></p> <p>Consider the following attempt to answer this query:</p> <pre><code>SELECT S.sname, MAX(S.age) FROM Sailors S </code></pre> <p>The intent is for this query to return not only the maximum age but also the name of the sailors having that age. However, this query is illegal in SQL--if the SELECT clause uses an aggregate operation, then it must use <em>only</em> aggregate operations unless the query contains a GROUP BY clause!</p> </blockquote> <p>Some time later while doing an exercise using MySQL, I faced a similar problem, and made a mistake similar to the one mentioned. However, MySQL didn't complain and just spit out some tables which later turned out not to be what I needed.</p> <p>Is the query above really illegal in SQL, but legal in MySQL, and if so, why is that? In what situation would one need to make such a query?</p> <p><strong>Further elaboration of the question:</strong></p> <p>The question isn't about whether or not all attributes mentioned in a SELECT should also be mentioned in a GROUP BY. It's about why the above query, using atributes together with aggregate operations on attributes, without any GROUP BY is legal in MySQL.</p> <p>Let's say the Sailors table looked like this:</p> <pre><code>+----------+------+ | sname | age | +----------+------+ | John Doe | 30 | | Jane Doe | 50 | +----------+------+ </code></pre> <p>The query would then return:</p> <pre><code>+----------+------------+ | sname | MAX(S.age) | +----------+------------+ | John Doe | 50 | +----------+------------+ </code></pre> <p>Now who would need that? John Doe ain't 50, he's 30! As stated in the citation from the book, this is a first attempt to get the name and age of the oldest sailor, in this example, Jane Doe at the age of 50.</p> <p>SQL would say this query is illegal, but MySQL just proceeds and spits out "garbage". Who would need this kind of result? Why does MySQL allow this little trap for newcomers?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload