Dylan Beaudette said the following:
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!
Although it is somewhat inefficient for large tables, the following
trick should work:
SELECT a.* from ca_subgroups as a left join ca_subgroups as b on
a.muid=b.muid and a.total_pct<b.total_pct where b.muid is null;
The reason this works is that is that the first clause of the ON will
match up muid. The second clause will come up with a match unless the
total_pct is at a maximum (for a given muid). In that case since there
is no record with total_pct greater than the maximum, all of the b
columns in the join for that record will be NULL. The WHERE clause
selects precisely those rows.
If two records have the same muid and maximum total_pct, both will be
selected. Your statement of your problem didn't deal with this issue.
Bruce Wolk
_______________________________________________
vox-tech mailing list
[email protected]
http://lists.lugod.org/mailman/listinfo/vox-tech
