How do I do this
Posted: Fri Jul 09, 2010 2:38 am
I am trying to run a 10 pin bowling league and I am trying to get a high score page for players and for teams. My problem is how do I get the high scores from three colums
|------------------| ------------------|------------------|
| Game 1 | Game 2 | Game 3 |
|------------------|-------------------|------------------|
200 190 150
191 170 201
189 180 175
I can do the following to get the highest score from each column individually
SELECT Game 1,PLAYERNAME FROM bowling.scores order by Game 1 desc;
result 200 player1 , 191 player2 , 189 player2
SELECT Game 2,PLAYERNAME FROM bowling.scores order by Game 2 desc;
result 190 player3 , 180 player1 , 170 player3
SELECT Game 3,PLAYERNAME FROM bowling.scores order by Game 3 desc;
result 201 player1 , 175 player1 , 150 player3
But what if the second highest score in Game 1 beats the highest score in Game 2. What do I do to sort this out?
|------------------| ------------------|------------------|
| Game 1 | Game 2 | Game 3 |
|------------------|-------------------|------------------|
200 190 150
191 170 201
189 180 175
I can do the following to get the highest score from each column individually
SELECT Game 1,PLAYERNAME FROM bowling.scores order by Game 1 desc;
result 200 player1 , 191 player2 , 189 player2
SELECT Game 2,PLAYERNAME FROM bowling.scores order by Game 2 desc;
result 190 player3 , 180 player1 , 170 player3
SELECT Game 3,PLAYERNAME FROM bowling.scores order by Game 3 desc;
result 201 player1 , 175 player1 , 150 player3
But what if the second highest score in Game 1 beats the highest score in Game 2. What do I do to sort this out?