l i n u x - u s e r s - g r o u p - o f - d a v i s
L U G O D
 
Next Meeting:
November 4: Social gathering
Next Installfest:
TBD
Latest News:
Oct. 24: LUGOD election season has begun!
Page last updated:
2005 Aug 28 14:48

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



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



LinkedIn
LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
facebook
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:
Sunset Systems
Who graciously hosts our website & mailing lists!