[thelist] MySQL Trouble: GROUP BY clauses

Joshua Olson joshua at waetech.com
Tue Mar 19 23:47:00 CST 2002


----- Original Message -----
From: "Andrew Forsberg" <andrew at thepander.co.nz>
Subject: [thelist] MySQL Trouble: GROUP BY clauses

> SELECT rate.rate_type_id, rate.id, MIN(rate.rate)
> FROM rate
> WHERE (rate.rate != 0)
> GROUP BY rate.rate_type_id
> ORDER BY rate.rate_type_id
>
> Gives me the correct minimum rate, but seemingly random rate ids... I
> can't quite put my finger on what I'm doing wrong, can anyone help
> please?

Andrew,

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?

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.

That last rule tends to stump most people.  The conceptual reason behind the
rule is this:

By having the GROUP BY clause, you are asking the SQL engine to do some sort
of *summary*.  Specifically, you are asking it to do summaries for all
elements with the same rate_type_id.

Since every item in each group will have the same rate_type_id, you can
return that particular piece of information.  The value represents a summary
for the group.

The aggregate function MIN() asks the SQL engine to return the Minimum value
for a particular fields within the group.  Again, that makes sense since we
are doing a summary.

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.

DETAIL cannot (again, cannot) be returned in a summarization query...
normally.

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:

SELECT
  rate.rate_type_id,
  MIN(rate.rate) AS rate,
  (SELECT r.id
   FROM rate r
   WHERE r.rate_type_id = rate.rate_type_id
   AND r.rate = MIN(rate.rate)) AS id
FROM rate
WHERE (rate.rate != 0)
GROUP BY rate.rate_type_id
ORDER BY rate.rate_type_id

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 just noticed you are using MySQL.  Unfortunately, I've only tested this
code on MSSQL.  I apologize if this last little bit does work on MySQL.

Either way, I hope I've answered your question on why your original query
did not work.

Good luck,
-joshua





More information about the thelist mailing list