Hello,
I think following query would help you
For Ascending
select cpid,sum(score),team from j group by cpid order by sum(score)
For Descending
select cpid,sum(score),team from j group by cpid order by sum(score) desc
Thanks!
2009/1/7 Phil <freedc....@gmail.com>
> A question on grouping I've never been able to solve...
>
> create table j (proj char(3), id int, score double,cpid char(32),team
> char(10));
>
> insert into j values('aaa',1,100,'aaaaaaaaaaaaaaaaa','team1');
> insert into j values('bbb',2,200,'aaaaaaaaaaaaaaaaa','team1');
> insert into j values('ccc',3,300,'aaaaaaaaaaaaaaaaa','team2');
> insert into j values('aaa',4,100,'bbbbbbbbbbbbbbbbb','team2');
> insert into j values('bbb',5,300,'bbbbbbbbbbbbbbbbb','team1');
> insert into j values('ccc',6,400,'bbbbbbbbbbbbbbbbb','team1');
> insert into j values('aaa',7,101,'ccccccccccccccccc','team1');
> insert into j values('bbb',8,302,'ccccccccccccccccc','team2');
> insert into j values('ccc',9,503,'ccccccccccccccccc','team2');
>
> mysql> select * from j;
> +------+------+-------+-------------------+-------+
> | proj | id | score | cpid | team |
> +------+------+-------+-------------------+-------+
> | aaa | 1 | 100 | aaaaaaaaaaaaaaaaa | team1 |
> | bbb | 2 | 200 | aaaaaaaaaaaaaaaaa | team1 |
> | ccc | 3 | 300 | aaaaaaaaaaaaaaaaa | team2 |
> | aaa | 4 | 100 | bbbbbbbbbbbbbbbbb | team2 |
> | bbb | 5 | 300 | bbbbbbbbbbbbbbbbb | team1 |
> | ccc | 6 | 400 | bbbbbbbbbbbbbbbbb | team1 |
> | aaa | 7 | 101 | ccccccccccccccccc | team1 |
> | bbb | 8 | 302 | ccccccccccccccccc | team2 |
> | ccc | 9 | 503 | ccccccccccccccccc | team2 |
> +------+------+-------+-------------------+-------+
> 9 rows in set (0.00 sec)
>
> mysql> select cpid,sum(score),team from j group by cpid;
> +-------------------+------------+-------+
> | cpid | sum(score) | team |
> +-------------------+------------+-------+
> | aaaaaaaaaaaaaaaaa | 600 | team1 |
> | bbbbbbbbbbbbbbbbb | 800 | team2 |
> | ccccccccccccccccc | 906 | team1 |
> +-------------------+------------+-------+
> 3 rows in set (0.00 sec)
>
> Using MAX or MIN on the team gives different but not necessarily closer
> results.
>
> mysql> select cpid,sum(score),max(team) from j group by cpid;
> +-------------------+------------+-----------+
> | cpid | sum(score) | max(team) |
> +-------------------+------------+-----------+
> | aaaaaaaaaaaaaaaaa | 600 | team2 |
> | bbbbbbbbbbbbbbbbb | 800 | team2 |
> | ccccccccccccccccc | 906 | team2 |
> +-------------------+------------+-----------+
> 3 rows in set (0.00 sec)
>
> mysql> select cpid,sum(score),min(team) from j group by cpid;
> +-------------------+------------+-----------+
> | cpid | sum(score) | min(team) |
> +-------------------+------------+-----------+
> | aaaaaaaaaaaaaaaaa | 600 | team1 |
> | bbbbbbbbbbbbbbbbb | 800 | team1 |
> | ccccccccccccccccc | 906 | team1 |
> +-------------------+------------+-----------+
> 3 rows in set (0.00 sec)
>
> Given that for cpid = 'bbbbbbbbbbbbbbb', they have 2 rows where it is
> team1,
> and only 1 with team2 but the original query gives team2 and rightly so as
> it just uses the first row in mysql's slightly illegal (but useful!) use of
> allowing other columns in the query but not in the group by.
>
> The question is, is there any way to modify this query so that it would
> return the team having the most entries?
>
> Theoretical what I would like:
>
> | cpid | sum(score) | team |
> +-------------------+------------+-------+
> | aaaaaaaaaaaaaaaaa | 600 | team1 |
> | bbbbbbbbbbbbbbbbb | 800 | team1 |
> | ccccccccccccccccc | 906 | team2 |
>
>
> If not, is there an easy way to have another column, say mostteam char(10)
> and run an update statement on the whole table which would put the correct
> value in?
>
> Regards
>
> Phil
>
> --
> Distributed Computing stats
> http://stats.free-dc.org
>
