Andrea Raimondi wrote:

> Hello.
>
> This is a prettily tricky query in fact.
> Here it is:
>
> select Messages.*,avg(MessageRatings.ratingvalue) as AvgRate
> from Messages
> join messageratings mg on mg.message_id = Messages.message_id
> group by AvgRate

That's not going to work ... How would you expect it to work?
You're asking Firebird to compute the average of a group whose
membership is determined by the average you're computing.

> I simply want to group messages by the ratings they received from
> users, that is - the ones higher rated get showed up before less
> rated ones. All, of course, relative to thread.

I think you don't understand what group by means in SQL. What
you're asking for, I think, is ordering. Do you get what you
want with this query?


select msgs.*,
(select avg (rtg.ratingvalue) from messagesratings rtg
where rtg.message_id = msgs.message_id) as AvgRate
from Messages msgs
order by AvgRate


I greatly prefer to see the actual list of fields from the
messages table, but ... In any even, if you use aliases, use
them consistently. Firebird V2 insists on that point.


Regards,


Ann