[vox-tech] mysql 4.0 query help

[timriley]

---------- Original Message ----------------------------------
From: Dylan Beaudette <dylan at iici.no-ip.org>
Reply-To: "lugod's technical discussion forum" <vox-tech at lists.lugod.org>
Date:  Sat, 27 Aug 2005 19:49:00 -0700

>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...

I've been following this thread with interest as there are quite
creative query suggestions. However, I can't help but to see the
straight forward group-by as what will work.

select muid, max( total_pct )
from ca_subgroups
group by muid;

I don't think this would return the first associated record.
What am I missing?

>
>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
>vox-tech at lists.lugod.org
>http://lists.lugod.org/mailman/listinfo/vox-tech
>