[vox-tech] mysql 4.0 query help

[David Hummel]

On Sun, Aug 28, 2005 at 10:05:52AM -0700, Bruce Wolk wrote:
> 
> Dylan Beaudette said the following:
> >
> >I am interested only in the record which contains the highest
> >total_pct value for a given muid value.
> >
> >I was just hoping for something a little more elegant.
> 
> SELECT a.* from ca_subgroups as a left join ca_subgroups as b on 
> a.muid=b.muid and a.total_pct<b.total_pct where b.muid is null;

This is a rather elegant solution.

> If two records have the same muid and maximum total_pct, both will be
> selected. Your statement of your problem didn't deal with this issue.

To avoid this you can group the resulting rows:

select a.*
from ca_subgroups as a
left join ca_subgroups as b on a.muid=b.muid and a.total_pct<b.total_pct
where b.muid is null
group by a.muid, a.total_pct, a.codename

In MySQL, just a.muid would suffice in the group by clause.  Grouping by
all columns (that aren't used in an aggregate function) is a bit more
robust across RDBMS's.

-David