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.
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();