sql aggregate question

From: Doug Edmunds <dae_alt3_at_juno.com>
Date: 17 Jan 2001 16:29:17 PDT
Message-ID: <Xns902CA6FC7edmundspacifiercom@199.2.117.165>

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 - 17:29:17 CST