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 [email protected] http://lists.lugod.org/mailman/listinfo/vox-tech
