Statistical Techniques - MySQL - page 544

-> ORDER BY wins-losses DESC, PCT DESC;
+-------------+------+------+-------+------+
| team | W | L | PCT | GB |
+-------------+------+------+-------+------+
| Winnipeg | 37 | 20 | 0.649 | - |
| Crookston | 31 | 25 | 0.553 | 5.5 |
| Fargo | 30 | 26 | 0.535 | 6.5 |
| Grand Forks | 28 | 26 | 0.518 | 7.5 |
| Devils Lake | 19 | 31 | 0.380 | 14.5 |
| Cavalier | 15 | 32 | 0.319 | 17.0 |
+-------------+------+------+-------+------+
These statements order the teams by win-loss differential, using winning percentage as
a tie-breaker in case there are teams with the same differential value. It's simpler to sort
by percentage, of course, but then you wouldn't always get the correct ordering. It's a
curious fact that a team with a lower winning percentage can actually be higher in the
standings than a team with a higher percentage. (This generally occurs early in the
season, when teams may have played highly disparate numbers of games, relatively
speaking.) Consider the case in which two teams, A and B, have the following rows:
+------+------+--------+
| team | wins | losses |
+------+------+--------+
| A | 4 | 1 |
| B | 2 | 0 |
+------+------+--------+
Applying the GB and percentage calculations to these team records yields the following
result, in which the first-place team actually has a lower winning percentage than the
second-place team:
+------+------+------+-------+------+
| team | W | L | PCT | GB |
+------+------+------+-------+------+
| A | 4 | 1 | 0.800 | - |
| B | 2 | 0 | 1.000 | 0.5 |
+------+------+------+-------+------+
The standings calculations shown thus far can be done without a join. They involve only
a single set of team records, so the first-place team's win-loss differential can be stored
in a variable. A more complex situation occurs when a dataset includes several sets of
team records. For example, the 1997 Northern League had two divisions (Eastern and
Western). In addition, separate standings were maintained for the first and second
halves of the season because season-half winners in each division played each other for
the right to compete in the league championship. The following table, standings2 ,
shows what these rows look like, ordered by season half, division, and win-loss differ‐
ential:
mysql> SELECT half, division, team, wins, losses FROM standings2
-> ORDER BY half, division, wins-losses DESC;