SQL vs MySQL: Rules about aggregate operations and GROUP BY

Posted by Phazyck on Stack Overflow See other posts from Stack Overflow or by Phazyck
Published on 2012-10-11T15:34:08Z Indexed on 2012/10/11 15:36 UTC
Read the original article Hit count: 258

In this book I'm currently reading while following a course on databases, the following example of an illegal query using an aggregate operator is given:

Find the name and age of the oldest sailor.

Consider the following attempt to answer this query:

SELECT S.name, S.age
FROM Sailors.S

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 only aggregate operations unless the query contains a GROUP BY clause!

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 be what I needed.

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about sql