Phil,
If in the 2nd query you want teams with the highest count per cpid found
in the first query, I think you can map the 'Avoiding repeat
aggregation' pattern
(http://www.artfulsoftware.com/infotree/queries.php) to your problem
PB
-----
Phil wrote:
Hi all,
got a simple problem I'm trying to solve without success.
Given the following table
CREATE TABLE `scores` (
`proj` char(3) NOT NULL default '',
`id` int(11) NOT NULL default '0',
`score` double default NULL,
`cpid` char(32) default NULL,
`team` char(20) default NULL,
PRIMARY KEY (`proj`,`id`),
KEY `cpid` (`cpid`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
insert into scores values ('a',1,100,'aaa','X');
insert into scores values ('b',2,50,'aaa','X');
insert into scores values ('c',2,500,'aaa','Y');
I have the following sql to get the sum of scores for the cpid (cross
project id)
select cpid,sum(score) from scores group by cpid;
This is simple enough and works fine. However I also wish to select the team
given this case, I'd like to get 'X' as there are two instances of 'X' and
only one of 'Y'
Is this possible in the same sql statement, something like an AVG for a
string, or a median perhaps.
Regards
Phil
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1346 - Release Date: 3/27/2008 10:03 AM
