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:
April 21: Google Glass
Next Installfest:
TBD
Latest News:
Mar. 18: Google Glass at LUGOD's April meeting
Page last updated:
2005 Aug 29 09:44

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 Sun, Aug 28, 2005 at 10:05:52AM -0700, Bruce Wolk wrote:
> 
> Dylan Beaudette said the following:
> >
> >I am interested only in the record which contains the highest
> >total_pct value for a given muid value.
> >
> >I was just hoping for something a little more elegant.
> 
> 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;

This is a rather elegant solution.

> 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.

To avoid this you can group the resulting rows:

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
group by a.muid, a.total_pct, a.codename

In MySQL, just a.muid would suffice in the group by clause.  Grouping by
all columns (that aren't used in an aggregate function) is a bit more
robust across RDBMS's.

-David
_______________________________________________
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:
EDGE Tech Corp.
For donating some give-aways for our meetings.