Order by COUNT(something) vs order by #column (sqlzoo example)

Question

I was trying to solve problem 15 under http://sqlzoo.net/wiki/More_JOIN_operations

I don't understand why my query is wrong, even though my output is like it's supposed to be.

Here's my query:

SELECT movie.title, COUNT(actorid)
FROM movie JOIN casting on movie.id=movieid
WHERE yr='1978'
GROUP BY casting.movieid
ORDER BY COUNT(casting.actorid) DESC

And the official answer:

  SELECT title, COUNT(actorid)
  FROM casting,movie                
  WHERE yr=1978
        AND movieid=movie.id
  GROUP BY title
  ORDER BY 2 DESC

If I just change the ORDER BY in my query from ORDER BY COUNT(casting.actorid) DESC to ORDER BY 2 DESC the answer is accepted (correct). Any reason for this?

Answer 1

All three of these should be accepted:

SELECT m.title, COUNT(c.actorid) as NumActors
FROM movie m JOIN
     casting c
     on m.id= c.movieid
WHERE yr = '1978'
GROUP BY c.movieid
ORDER BY COUNT(c.actorid) DESC

and:

ORDER BY 2 DESC

and:

ORDER BY NumActors DESC

Kudos for using proper explicit JOIN syntax. Simple rule: do not use commas in the FROM clause.

As a note: I think the use of 2 might be removed from some future version of the ANSI-compliant databases.

Answer 2

Yes - you can sort by the column ordinal. However you need to be aware that if the select list changes (if columns are added or removed, if the columns order is changed, etc..) you will see unexpected results if the order by clause is not modified to reflect the changes.

Some RDMBS's will allow you to give the column an alias and to use this in the order by clause. Not sure if MySql is one of those however.

SELECT movie.title, COUNT(actorid) NumMovies
FROM movie JOIN casting on movie.id=movieid
WHERE yr='1978'
GROUP BY casting.movieid
ORDER BY NumMovies DESC