This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The rationale for this rule is as follows. Suppose you have a list of cities and countries. If this SQL statement were legal:

SELECT    city, country FROM Cities_And_Countries 
GROUP BY country;

what value would come out in the CITY Column? There are plausible answers such as "any city will do provided it's in the country", or "the DBMS should assume that we want to group by both country and city". The problem with those answers is that they try to compensate for a formal user error. What the user really needs to know is "that does not compute".

The rule does not mean that all values must be distinct. We could multiply everything times zero, yielding zeros in every Column in the select list, and we would still be specifying "single-valued per group". The true meaning is that there must be, for each group, one (and only one) value which is appropriate as an answer for the query. Sometimes the DBMS doesn't realize this, as in a SQL statement like:

SELECT   capitalcity, country FROM Cities_And_Countries 
GROUP BY country;

but if that's the case, we can easily tell the DBMS this is so by adding a grouping Column to the statement:

SELECT   capitalcity, country FROM Cities_And_Countries 
GROUP BY country, capitalcity;

The single-value rule is sensible and it is Standard SQL. Don't be misled by a "textbook" describing the one DBMS which does not follow the Standard.

Comments

Comments loading...