[vox-tech] mysql 4.0 query help

[Bruce Wolk]

Dylan Beaudette said the following:
> 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.
> 
> Thanks in advance!

Although it is somewhat inefficient for large tables, the following 
trick should work:

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;

The reason this works is that is that the first clause of the ON will 
match up muid. The second clause will come up with a match unless the 
total_pct is at a maximum (for a given muid). In that case since there 
is no record with total_pct greater than the maximum, all of the b 
columns in the join for that record will be NULL. The WHERE clause 
selects precisely those rows.

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.

Bruce Wolk