title: ORDER BY with CASE MySQL
body: <p>I have a "points standings" query, which shows statistics for a league I am in. I am attempting to order the query based off a few conditions.</p>
  
  <ol>
  <li>If the team is ranked 1-25 in points, order by wins, points</li>
  <li>If the team is ranked outside the top 25 (26th to 42nd in points), order by points.</li>
  </ol>
  
  <p>The best way I could describe it, is that it should act like two tables. Teams ranked (1-25) should be sorted together, and teams ranked (26-42) should be sorted together.</p>
  
  <p>Here is my query.</p>
  
  <pre><code>SELECT m.Team AS team
  , SUM( r.points ) AS points
  , SUM(CASE
      WHEN rank = 1 THEN 1
      ELSE 0
    END) AS Wins
  , SUM(CASE
      WHEN rank &lt; 6 THEN 1
      ELSE 0
    END) AS T5
  , SUM(CASE
      WHEN rank &lt; 11 THEN 1
      ELSE 0
    END) AS T10
  , SUM(CASE
      WHEN rank &lt; 21 THEN 1
      ELSE 0
    END) AS T20
  , ROUND(Avg(r.points),2) AS ppr
  , ROUND(Avg(r.rank),2) as avg_finish
  , MIN(r.rank) as best
  FROM members m
  LEFT JOIN results r ON r.team_id = m.M_ID
  GROUP BY team
  ORDER BY 
      CASE
          WHEN COUNT(*) &lt; 26
          THEN Wins 
          WHEN COUNT(*) &gt; 25
          THEN points
      END DESC, points DESC;
  </code></pre>
  
  <p>I receive an error "Reference 'Wins' not supported (reference to group function) due to my alias "Wins". Instead, I've tried:</p>
  
  <pre><code>ORDER BY 
      CASE
          WHEN COUNT(*) &lt; 26
          THEN SUM(CASE
              WHEN rank = 1 THEN 1
              ELSE 0
          END)  
          WHEN COUNT(*) &gt; 25
          THEN points
      END DESC, points DESC;
  </code></pre>
  
  <p>This successfully orders my table, but does not factor the COUNT(*) > 25 CASE clause, and just orders by wins, then points. Any ideas on how I could refactor the ORDER BY clause?</p>