Re: sql aggregate question

select team, name, score from players A

        where score = ( select max( score)
                                 from players B
                                 where A.PLAYER = B.PLAYER
                                     and A.TEAM = B.TEAM );

Would return:
A job 20
A bob 20
B bill 30
C leo 50

( Not necessarilty in that order).

Doug Edmunds wrote:

> Given a table players
> with fields
> name char, score number, team char
>
> and data thus
> tom, 10, A
> joe, 20, A
> bob, 20, A
> fred, 20, B
> mark, 25, B
> bill, 30, B
> alex, 30, C
> mike, 40, C
> leo, 50, C
>
> how do I modify
>
> select max(score), team
> from players
> group by team;
>
> so it includes the name of the player(s)
> with the high score on each team?
>
> results wanted:
> joe, 20, A
> bob, 20, A
> bill, 30, B
> leo, 50, C
>
> Thanks.
> -- doug edmunds
> 17 Jan 2001
  Received on Wed Jan 17 2001 - 18:35:05 CST