title: SQL ORDER BY with column number or aggregate function
body: <p>I've got 2 different results when I try <code>ORDER BY</code> with column number and with an aggregate function. What is the difference between these 2 methods? ( I thought they'd have the same output)</p>
  
  <blockquote>
    <p>List the 1978 films by order of cast list size. There're 3 tables below:  </p>
  </blockquote>
  
  <pre><code>movie(id, title, yr, director)  
  actor(id, name)  
  casting(movieid, actorid, ord)  
  </code></pre>
  
  <blockquote>
    <p>Answer 1 using ORDER BY with column number:  </p>
  </blockquote>
  
  <pre><code>SELECT title
         ,COUNT(a.id)  
  FROM   movie m
         ,casting c
         ,actor a  
  WHERE  m.id=movieid 
  AND    a.id=actorid 
  AND    yr=1978  
  GROUP BY title  
  ORDER BY 2 DESC  
  </code></pre>
  
  <p>Using <code>COUNT(a.id)</code>. Everything is the same except the last line </p>
  
  <pre><code>...  
  ORDER BY COUNT(a.id) DESC  
  </code></pre>