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 28 10:16

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)
[vox-tech] mysql 4.0 query help
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[vox-tech] mysql 4.0 query help


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

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.