[vox-tech] mysql 4.0 query help

[Dylan Beaudette]

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!


--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341