[thelist] MySQL Trouble: GROUP BY clauses

[Andrew Forsberg]

Hi Joshua

Thanks, I think you're right:

> Let's me make sure I understand the problem, first off.  You have a bunch
of
> rates, and you want to find the minimum rate per rate type and return the
id
> of the rate that was the minimum rate per group?

Yes, so for the more common simple queries the app doesn't have to grind
through several tables to find the lowest rate for a type. The results from
this select will be inserted into another table to act as a cache. If the id
(well, it's another column, but that doesn't change the situation) for the
original table could also be inserted then so much the better, but it's not
necessary.

> Everything in your query is fine, until you added rate.id back into the
> SELECT clause.  As a general rule of thumb, you can only have the group by
> fields, and aggregate fields in the SELECT clause of a query having a
GROUP
> BY.

This makes sense, I think if it was 9am in the morning it would have
occurred to me that I'm trying to do something that is self-contradictory...
:-)

> If you are in the mindset that the SQL engine is now operating 100%
> differently than it does when it's not doing a summary, then it'll become
> apparent why selecting a rate.id will not return anything useful.  Heck,
> some SQL Engines will return an error!  rate.id is DETAIL of a particular
> record and has no purpose in a summary.

Yea! I got some really weird output with some variations on the submitted
query. To give MySQL its dues, it did report errors on about half of them...

> If, however, you know that there will be exactly one record with the
minimum
> value for each group, you can force the detail to be returned via a
> subselect:
[...]
> If you are wrong--and there are multiple rates in a group that share the
> honor of being the lowest--the query will crash when the engine determines
> the sub-query returns multiple results, so be careful.

I did think a subselect might be handy, but there's still no support for it
in MySQL, besides your caveat is an excellent warning against doing that
anyway. It is entirely possible that more than one row will have the lowest
rate. The best solution here is, as you've said, not to attempt to confuse
the summary information with the data itself. If non-summary info is
required I'll just go back to the real table.

Thanks again,
Andrew