[vox-tech] mysql 4.0 query help

[David Hummel]

On Sat, Aug 27, 2005 at 10:04:52PM -0500, Ken Bloom wrote:
> 
> On 8/27/05, Dylan Beaudette <dylan at iici.no-ip.org> wrote:
> > 
> > I am interested only in the record which contains the
> > highest total_pct value for a given muid value.
> 
> In ECS165a we learned to do this using a subquery:
> 
> select muid, total_pct, codename
> from ca_subgroups outer
> group by muid 
> having total_pct=(
>    select max(total_pct) from ca_subgroups 
>    where muid=outer.muid
> );

Not quite.  Use this instead:

select a.*
from ca_subgroups as a
where a.total_pct = (
  select max(total_pct)
  from ca_subgroups as b
  where b.muid = a.muid
)

But this doesn't help Dylan much since he's on MySQL 4.0.

-David