l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
July 7: Social gathering
Next Installfest:
Latest News:
Jun. 14: June LUGOD meeting cancelled
Page last updated:
2005 Aug 29 10:32

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
Re: [vox-tech] mysql 4.0 query help
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [vox-tech] mysql 4.0 query help

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

>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
>vox-tech mailing list
vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.