Database Reference
In-Depth Information
mysql>
SET @rownum = 0, @rank = 0, @prev_val = NULL;
mysql>
SELECT @rownum := @rownum + 1 AS row,
->
@rank := IF(@prev_val<>wins,@rownum,@rank) AS rank,
->
name,
->
@prev_val := wins AS wins
->
FROM al_winner ORDER BY wins DESC;
+------+------+----------------+------+
| row | rank | name | wins |
+------+------+----------------+------+
| 1 | 1 | Mulder, Mark | 21 |
| 2 | 2 | Clemens, Roger | 20 |
| 3 | 2 | Moyer, Jamie | 20 |
| 4 | 4 | Garcia, Freddy | 18 |
| 5 | 4 | Hudson, Tim | 18 |
| 6 | 6 | Zito, Barry | 17 |
| 7 | 6 | Sabathia, C.C. | 17 |
| 8 | 6 | Mussina, Mike | 17 |
| 9 | 6 | Mays, Joe | 17 |
| 10 | 6 | Abbott, Paul | 17 |
| 11 | 11 | Buehrle, Mark | 16 |
| 12 | 12 | Milton, Eric | 15 |
| 13 | 12 | Pettitte, Andy | 15 |
| 14 | 12 | Radke, Brad | 15 |
| 15 | 12 | Sele, Aaron | 15 |
+------+------+----------------+------+
15.12. Computing Team Standings
Problem
You want to compute team standings from their win-loss records, including the games-
behind (GB) values.
Solution
Determine which team is in first place, then join that result to the original rows.
Discussion
Standings for sports teams that compete against each other is a ranking problem, but
ranks are not based on a single measure as in
Recipe 15.11
. Standings are based on two
values, wins and losses. Teams are ranked according to which has the best win-loss
record, and teams not in first place are assigned a “games-behind” value indicating how
many games out of first place they are. This section shows how to calculate those values.
The first example uses a table containing a single set of team records to illustrate the
logic of the calculations. The second example uses a table containing several sets of
records (that is, the records for all teams in both divisions of a league, for both halves
