Get the highest number from several rows

[fran]

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???

[Barand]

Sorry but I am having great difficulty understanding how the required results can come from your sample data

[Barand]

Also, you should store the scores in a separate table, one row per team per game

+-------------+          +-------------+

| team        |          |  score      |

+-------------+          +-------------+

| team_id    | --------< | team_id    |

| team_name  |          | game        |

+-------------+          | score      |

                          +-------------+

 

so your scores table would look like this

+-------+----------+---------+

| team  | game    | score  |

+-------+----------+---------+

|  1    |  1      |  180    |

|  1    |  2      |  240    |

|  1    |  3      |  210    |

|  2    |  1      |  400    |

|  2    |  2      |  252    |

|  2    |  3      |  236    |

etc

 

[fran]

For the individual scores, this works.

 

<? $sql = "SELECT name, GREATEST(pins1, pins2, pins3) AS high_score FROM bc_averages WHERE league = '$list[league]' AND week = $list[week] ORDER BY high_score DESC LIMIT 4";

 

The problem is now I want the sum of the team scores.  So basically I need

 

<? $sql = "SELECT team, GREATEST(sum(pins1), sum(pins2), sum(pins3)) AS high_score FROM bc_averages WHERE league = '$list[league]' AND week = $list[week] ORDER BY high_score DESC LIMIT 4";

 

And of course, this doesn't work.

 

I am going to try to create a new table. 

[Barand]

Shouldn't the output from your data be

TEAM    Top Games

Team 4  410

Team 2  400

Team 4  394

[fran]

You are correct.  I goofed. 

 

 

[Barand]

If you reorganise the data as I suggested then simply

 

SELECT t.name, s.score
FROM team t
INNER JOIN score s ON t.team_id = s.team_id
ORDER BY s.score DESC
LIMIT 3

 

For the sum of the games it would be

SELECT t.name, SUM(s.score) as tot
FROM team t
INNER JOIN score s ON t.team_id = s.team_id
GROUP BY t.name
ORDER BY tot DESC
LIMIT 3

[fran]

I did finally go back and added a table.  It took a lot of thought but it works now. They can now add the scores per game and the scripts all do the math for them. Thank you for all your help.  I have learned a lot from this site.