Re: Embarassing GROUP question

Corey Tisdale <corey(at)eyewantmedia(dot)com> writes:
> SELECT
> meaningful_data,
> event_type,
> event_date
> FROM
> event_log
> GROUP BY
> event_type
> ORDER BY
> event_date DESC

Is event_type a primary key, or at least a candidate key, for this
table? (I would guess not based on the name.)

If it is, then the above is actually well-defined, because there is
only one possible input row for each group. The GROUP BY is actually
kinda pointless in that case.

If it is not, then the above is *not* well-defined --- there are
multiple possible meaningful_data and event_date values for each
event_type value, and you have absolutely no idea which ones you
will get. This is not allowed per SQL standard, and MySQL has
done you no service by failing to detect the ambiguity.

What you might be after is something like Postgres' DISTINCT ON
feature, which allows you to resolve the ambiguity by specifying
a sort order for the rows within each group (and then taking the
first row in each group). See the "weather reports" example in
our SELECT reference page.

I have never really played around with this aspect of MySQL ...
but looking at this example, and presuming that you find that
it actually does something useful, I wonder whether they interpret
the combination of GROUP BY and ambiguous-per-spec ORDER BY
in some fashion similar to DISTINCT ON.

regards, tom lane