[vox-tech] mysql 4.0 query help

[Ken Bloom]

On 8/27/05, Dylan Beaudette <dylan at iici.no-ip.org> wrote:
> Hi,
> 
> I am having some trouble working out the best way to get my data out of
> a table stored in MySQL.
> 
> Here is an example of the table structure:
> +-------+-----------+---------------+
> | muid  | total_pct | codename      |
> +-------+-----------+---------------+
> | CA001 |         2 | argixerolls   |
> | CA001 |         1 | haploxeralfs  |
> | CA001 |         1 | haploxerolls  |
> | CA001 |         1 | vitrandepts   |
> | CA001 |         3 | vitrixerands  |
> | CA001 |        11 | xerochrepts   |
> | CA001 |         1 | xeropsamments |
> | CA001 |        77 | xerumbrepts   |
> | CA002 |         1 | argixerolls   |
> | CA002 |        51 | haploxeralfs  |
> | CA002 |         1 | haploxerolls  |
> | CA002 |        16 | vitrixerands  |
> | CA002 |        21 | xerochrepts   |
> | CA002 |         1 | xerorthents   |
> | CA002 |         2 | xerumbrepts   |
> 
> As demonstrated above there are multiple muid values, each with a
> total_pct value. I am interested only in the record which contains the
> highest total_pct value for a given muid value. I have tried using the
> max() function with a group by clause, but this does not accomplish
> what I want. Instead, I get the first record associated with a given
> muid value - as would be expected apparently...
> 
> Since I am running MySQL 4.0 , subselects are not possible. I tried
> using a simple join to retrieve the desired data, but so far I have not
> been successful. Here is an example of the simple join method I was
> trying to use:
> 
> select a.muid,  b.*  from ca_subgroups as a, ca_subgroups as b group by
> a.muid having  max(a.total_pct) = b.total_pct;
> 
> where ca_subgroups is the name of the table described above. This query
> results in no records matched...
> 
> I suppose that I could create a temporary table containing only muid
> and max(total_pct) along with a join to the original table... I was
> just hoping for something a little more elegant.
> 

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);

--Ken Bloom