Database Reference
In-Depth Information
+------+----------+-----------------+------+--------+
| half | division | team | wins | losses |
+------+----------+-----------------+------+--------+
| 1 | Eastern | St. Paul | 24 | 18 |
| 1 | Eastern | Thunder Bay | 18 | 24 |
| 1 | Eastern | Duluth-Superior | 17 | 24 |
| 1 | Eastern | Madison | 15 | 27 |
| 1 | Western | Winnipeg | 29 | 12 |
| 1 | Western | Sioux City | 28 | 14 |
| 1 | Western | Fargo-Moorhead | 21 | 21 |
| 1 | Western | Sioux Falls | 15 | 27 |
| 2 | Eastern | Duluth-Superior | 22 | 20 |
| 2 | Eastern | St. Paul | 21 | 21 |
| 2 | Eastern | Madison | 19 | 23 |
| 2 | Eastern | Thunder Bay | 18 | 24 |
| 2 | Western | Fargo-Moorhead | 26 | 16 |
| 2 | Western | Winnipeg | 24 | 18 |
| 2 | Western | Sioux City | 22 | 20 |
| 2 | Western | Sioux Falls | 16 | 26 |
+------+----------+-----------------+------+--------+
Generating the standings for these rows requires computing the GB values separately
for each of the four combinations of season half and division. First, calculate the win-
loss differential for the first-place team in each group and save the values into a separate
firstplace
table:
mysql>
CREATE TEMPORARY TABLE firstplace
->
SELECT half, division, MAX(wins-losses) AS wl_diff
->
FROM standings2
->
GROUP BY half, division;
Then join the
firstplace
table to the original standings, associating each team record
with the proper win-loss differential to compute its GB value:
mysql>
SELECT wl.half, wl.division, wl.team, wl.wins AS W, wl.losses AS L,
->
TRUNCATE(wl.wins/(wl.wins+wl.losses),3) AS PCT,
->
IF(fp.wl_diff = wl.wins-wl.losses,
->
'-',TRUNCATE((fp.wl_diff - (wl.wins-wl.losses)) / 2,1)) AS GB
->
FROM standings2 AS wl INNER JOIN firstplace AS fp
->
ON wl.half = fp.half AND wl.division = fp.division
->
ORDER BY wl.half, wl.division, wl.wins-wl.losses DESC, PCT DESC;
+------+----------+-----------------+------+------+-------+------+
| half | division | team | W | L | PCT | GB |
+------+----------+-----------------+------+------+-------+------+
| 1 | Eastern | St. Paul | 24 | 18 | 0.571 | - |
| 1 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 6.0 |
| 1 | Eastern | Duluth-Superior | 17 | 24 | 0.414 | 6.5 |
| 1 | Eastern | Madison | 15 | 27 | 0.357 | 9.0 |
| 1 | Western | Winnipeg | 29 | 12 | 0.707 | - |
| 1 | Western | Sioux City | 28 | 14 | 0.666 | 1.5 |
| 1 | Western | Fargo-Moorhead | 21 | 21 | 0.500 | 8.5 |
| 1 | Western | Sioux Falls | 15 | 27 | 0.357 | 14.5 |
| 2 | Eastern | Duluth-Superior | 22 | 20 | 0.523 | - |
