sorting

[non_zero]

i'm going to try to explain this as clear as i can. but the explanation i made confuses even me.

i have a table named ratings. each time somebody rates something, a new entry is made under the field "rate", and object_id for what they rated.

i want to sort the objects by their rating. their rating is the average of the field "rate". i would normally get the rating by saying:

SELECT AVG(rate) FROM ratings WHERE object_id=1;

and since i'm a novice in mysql, i tried to solved my problem by saying:

SELECT * FROM ratings WHERE object_id=1 ORDER BY AVG(rate)

obviously... that didn't work.

i don't suppose there's a way to consolidate what i want to do into a mysql query, is there?

[Lars_Berg]

select avg(rate) as average_rate,object_id
from ratings 
group by object_id
order by average_rate desc

[non_zero]

works. thank you.