[thelist] MySQL Trouble: GROUP BY clauses

[rudy]

hi joshua

first of all, that was a great explanation of grouping in sql

you're right, it's a bit tricky to understand why the select list can only
return columns in the group by clause

it's too bad mysql relaxes this rule and lets you write non-standard
queries (although they do warn you against it)

your solution below uses sql-99 syntax, and there's another way

>  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.

an alternate approach, using sql-92 (or sql-86, i'm not really sure), is

   select rate.rate_type_id
        , rate.rate
     from rate
    where rate.rate =
          ( select min(r.rate)
              from rate as r
             where r.rate != 0
               and r.rate_type_id = rate.rate_type_id
          )
 group by rate.rate_type_id
 order by rate.rate_type_id

the advantage here is that it does not crash if the subquery returns
multiple rows -- in fact, the subquery *cannot* return multiple rows

but your point about multiple rows in each rate_type_group sharing the
honour of having the lowest rate is still good -- these rows will all be
returned by my outer query, as expected and probably as desired (e.g. if
the query is to find the name of the salesperson with the highest sales in
the month, you *do* want ties announced)

andrew, as far as how to do it without subqueries, mysql recommends two
separate queries with a temporary table, and that's a good approach too --
at least, it's way better than bringing lots o' data into your app and
doing the max logic within each group there


rudy
http://rudy.ca/