title: Complicated football league Dynamic Ordering in MySQL?
body: <p>I have a table 'games' for a football league as follows:</p>
  
  <pre><code>date    home_team_id    away_team_id    home_score      away_score
   -          1                 2              6             21
   -          3                 1              7             19
  </code></pre>
  
  <p>I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then points for if poss)?</p>
  
  <p>--</p>
  
  <p>I have this query which works fine when I have a $team_id but of cause then I can only do 1 team at a time, and that doesn't allow for ordering at query level</p>
  
  <pre><code>((SELECT COUNT(*) FROM `games` WHERE ((`home_score` &gt; `away_score`) AND `home_team_id` = '.$team_id.')) + 
  (SELECT COUNT(*) FROM `games` WHERE ((`home_score` &lt; `away_score`) AND `away_team_id` = '.$team_id.'))) AS `wins`
  </code></pre>
  
  <p>I wonder if i can use this with some form of GROUP, or mySQL can know the $team_id itself?
  I've also tried some multiple JOINs with the 'team' table but they didn't work either.</p>
  
  <p>Thanks,</p>
  
  <p>Dan</p>