0

In this article about ANSI SQL 99 standard requirement group by they say:

the statement’s select list may consist only of references to Columns that are single-valued per group – this means that the select list can’t include a reference to an interim result Column that isn’t also included in the GROUP BY clause

But in MySQL we can do this:

select b
from a
group by c

and it doesn't complain about it, so i would like to know if this is considered a violation of the standard.

I've already read this answer about this topic, but i would like to know if this is a violation, or can be considered as it (let's say there is an example that says "be strictly to the SQL standard" and i wrote that as result), not why it works or something else.

Alberto Sinigaglia
  • 12,097
  • 2
  • 20
  • 48
  • Each DBMS may have (and have in practice) a lot of extensions which does not match (but not contracicts!) to the standard. MySQL is not an exclusion. Yes it allows partial grouping. This extension may be enabled or disabled by setting the SQL Mode, more precisely `ONLY_FULL_GROUP_BY` option. If it is present in modes list then partial grouping is not allowed and causes error, if it is absent then partial grouping is allowed. – Akina Feb 12 '20 at 12:41

1 Answers1

2

I originally misunderstood the question.

MySQL no longer supports:

select b
from a
group by c;

(at least using default settings). Yay! This returns an error just as it should and as it does in almost all other databases (I think SQLite might be the last hold-out now).

This IS a violation of the standard -- in general (see below). The problem is that one row is returned per c value. Anything other than c in the GROUP BY should be an argument to a GROUP BY.

There is one case where this is allowed. That is when c is a primary or unique key in a (technically called "functionally dependent"). In this case, you can select other columns from the table without aggregation functions. Postgres is one of the databases that supports this.

If you want one value of b per c, then the canonical solution is something like:

select max(b)
from a
group by c;

Postgres also offers distinct on which allows more flexibility:

select distinct on (c) b
from a
order by c, random();
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Berto99 . . . Good morning. I totally misunderstood the original question. I have written what is a correct answer for you. – Gordon Linoff Feb 12 '20 at 12:28
  • Hi, thanks for the answer.. is also a violation when there is the functional dependency between the select field and the one in the group by? currently i'm using 10.1.44-MariaDB and it shows no warnings/errors in that case – Alberto Sinigaglia Feb 12 '20 at 12:31
  • @Berto99 . . . That is based on an older version of MySQL. Upgrade to a more recent version. – Gordon Linoff Feb 12 '20 at 12:39
  • *then the canonical solution is something like* No, canonical solution is in special function use - [ANY_VALUE()](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value). – Akina Feb 12 '20 at 12:43
  • @Akina . . . I am not aware that `ANY_VALUE()` is a standard SQL aggregation function. Do you have a reference that it is? – Gordon Linoff Feb 12 '20 at 13:25
  • This is canonical for MySQL. – Akina Feb 12 '20 at 13:27