EXPERT RESPONSE
What a great question. Sincerely. Your
interest in how things work, or should work, or might work, is
commendable. In particular, your objective of portability
shows good insight into what makes a database application a good
database application or an albatross.
So this may not be a good time to mention MySQL's quirky support
of the GROUP BY clause. MySQL allows so-called "hidden fields" in a
grouping SQL statement, allowing you to omit columns from the GROUP BY
but keep them in the SELECT list. They explain how it works, and the reason
why this syntax is allowed, on the documentation's
12.9.3 GROUP BY with Hidden Fields page, which includes the warning:
Do not use this feature if the columns you omit
from the GROUP BY part are not unique in the group! You will get
unpredictable results.
This warning used to be in bold, so they've toned it down.
Note the exasperation in the exclamation mark.
But you're asking about the reverse situation, extra
columns in the GROUP BY that aren't in the SELECT.
Is it okay to GROUP BY a,b,c but omit c from the SELECT list?
My advice may not be worth much, as it comes of experience, not theory.
(If you want standards, you want Joe Celko. My knowledge of standards
is nowhere near his.)
My advice is to write your SQL so that it will work in as many databases
as you're ever going to target. Make sure it conforms to at least one of the
published standards, and uses nothing deprecated in a later one.
If in doubt, use the Mimer Validator.
So, is it okay to write a GROUP BY on columns
that aren't in the SELECT? If you get different results
in different databases, and if portability matters, then obviously, no.
Is there a way around this? Yes, but that's a different question.
|
