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:
August 5: Social gathering
Next Installfest:
TBD
Latest News:
Jul. 4: July, August and September: Security, Photography and Programming for Kids
Page last updated:
2005 Aug 27 22:08

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



On 8/27/05, Dylan Beaudette <dylan@iici.no-ip.org> wrote:
> 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.
> 

In ECS165a we learned to do this using a subquery:

select
   muid, total_pct, codename
from
   ca_subgroups outer
group by 
   muid 
having total_pct=(
   select 
      max(total_pct) 
   from 
      ca_subgroups 
   where 
      muid=outer.muid);

--Ken Bloom
_______________________________________________
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:
Appahost Applications
For a significant contribution towards our projector, and a generous donation to allow us to continue meeting at the Davis Library.