Get the highest number from several rows

franwpd

I have been working on this script and finally got it this far but now I am stuck. It shows the team and their scores from 3 games.

SELECT team, SUM(game1) AS game1, SUM(game2) AS game2, SUM(game3) AS game3 FROM totals WHERE league = '$league' AND week = '$week' GROUP BY team";
$sql_result = mysql_query($sql,$link) or die(mysql_error());

while ($row = mysql_fetch_array($sql_result)) {

Team Game1 Game2 Game3
Team 1 180 240 210
Team 2 400 252 236
Team 3 294 258 310
Team 4 394 358 410

I need find the top 3 teams with the highest scores from all the games. So it should print this:

TEAM Top Games
Team 1 410
Team 2 400
Team 3 394

Help???

Derokorian

SELECT MAX(SUM(game1),SUM(game2),SUM(game3)) as BestGame From table....

Actually that probably won't work, but this might:

SELECT MAX(games) FROM (
   (SELECT SUM(game1) as games FROM table)
      UNION
   (SELECT SUM(game2) as games FROM table)
      UNION
   (SELECT SUM(game3) as games FROM table)
)

Alternatively you should probably look at normalizing your database structure. Any time you have numbered columns its a sure sign that your database isn't properly normalized. Believe it or not the wikipedia article is pretty good http://en.wikipedia.org/wiki/Database_normalization