That's because in a GROUP BY query that sort of restriction has to go in a HAVING clause. What you're doing is called "group-wise maximum" and is a common problem with SQL. There's a page with several good solutions in the MySQL docs.

For future MySQL questions, I recommend asking on the MySQL users mailing list.