SQL Statement????

Trying to write an sql script to sort my teams by rating.  Problem is I have to use the avg feature and distinct feature to get the average for each team.  It then sorts it by teamname instead of rating.

rs.Open "SELECT DISTINCT TeamName, avg(rating) AS Average_rating FROM ratings GROUP BY teamname

It will not let me group by Average_rating
gives me This error
Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'TeamName' as part of an aggregate function

You can not use order by with DISTINCT

How could you sort it then?

I figured out how to do it though.  I used the max () feature and it worked.

rs.Open "SELECT DISTINCT max([TeamName])as Team, avg(rating) AS Average_rating FROM ratings GROUP BY teamname ORDER BY avg(rating) DESC"