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
_______________________________________________
vox-tech mailing list
[email protected]
http://lists.lugod.org/mailman/listinfo/vox-tech
