MongoUK MongoUK - 2 months ago 10
PHP Question

How to order a sports league table in MySQL?

I already have a league standings table, however, I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Currently shows like below, but technically Team2 should have won:

Team W L T PF PA PCT
—————————————————————————————————
Team1 7 3 0 247 139 0.7000
Team2 6 2 2 220 122 0.6000
Team3 6 4 0 191 191 0.6000
Team4 4 5 1 167 201 0.4000
Team5 3 6 1 142 202 0.3000
Team6 2 8 0 193 305 0.2000


This is the SQL:

SELECT team
, COUNT(*) played
, SUM(win) wins
, SUM(loss) lost
, SUM(win)/count(*) pctWon
, SUM(draw) draws
, SUM(SelfScore) ptsfor
, SUM(OpponentScore) ptsagainst
, SUM(SelfScore) - SUM(OpponentScore) goal_diff
, SUM(3*win + draw) score
FROM (
SELECT team
, SelfScore
, OpponentScore
, SelfScore > OpponentScore win
, SelfScore < OpponentScore loss
, SelfScore = OpponentScore draw
FROM (
SELECT HomeTeam team, HomeScore SelfScore, AwayScore OpponentScore
FROM Game
union all select AwayTeam, AwayScore, HomeScore
FROM Game
) a
) b
GROUP BY team
ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;

abl abl
Answer

Take a look at your ORDER BY clause: you are effectively asking for the results to be ordered by order of wins descending. This rule takes preference over all the others, so obviously Team1 wins.

ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;

I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Then that would be:

ORDER BY (wins*2 + draws) DESC, lost ASC, ptsagainst DESC;