Reputation: 159
I am organizing a golf tournament in which players can submit up to 10 scorecards. The criteria for choosing the winner is forming an ideal score card with the best holes of all the score cards submited by the user.
For those unfamiliar with the sport, one scorecard has 18 holes.
So my question is how to select the ideal score card within one sql query
+----------------------------------+
+ player | hole1 | hole2 | hole3...+
+----------------------------------+
+ john | 4 | 3 | 5 +
+ john | 3 | 5 | 4 +
+ paul | 6 | 3 | 4 +
+ paul | 2 | 5 | 4 +
+----------------------------------+
Now the output should be
+----------------------------------+
+ player | hole1 | hole2 | hole3...+
+----------------------------------+
+ john | 3 | 3 | 4 +
+ paul | 2 | 3 | 4 +
+----------------------------------+
My question is how can i make this select with a sql query.
Any thoughts? i would be really glad if someone can help me with this.
Upvotes: 0
Views: 157
Reputation: 3760
Try something along this:
SELECT player, MIN(hole1), MIN(hole2) ... FROM scores GROUP BY player
Upvotes: 2