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

WHAT I WANT TO DO
-----------------

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.

This is my database structure:

CREATE TABLE MESSAGES (
MESSAGE_ID INTEGER NOT NULL,
PARENTMESSAGE_ID INTEGER,
USER_ID INTEGER NOT NULL,
SUBJECT VARCHAR(255) NOT NULL,
MESSAGEBODY BLOB SUB_TYPE 1 SEGMENT SIZE 1000 NOT NULL
);

CREATE TABLE MESSAGERATINGS (
RATINGID INTEGER NOT NULL,
MESSAGE_ID INTEGER NOT NULL,
RATINGVALUE SMALLINT NOT NULL
);

Let's make an example:

Say that I have the tables this way:

MESSAGE_ID PARENTMESSAGE_ID SUBJECT MESSAGEBODY
1 0 Very Valuable message Indeed very valuable
2 1 Not very valuable This one's ok
response, still ok tho
3 1 Awful response This one's not worth
4 0 Awful message This one's not worth
5 4 Very valuable response Indeed very valuable
6 4 Awful response This one's not worth
7 4 Not very valuable This one's ok
response, still ok tho
Now we have the ratings table:

RATING_ID MESSAGE_ID RATINGVALUE
1 1 09
2 2 07
3 3 02
4 4 01
5 5 09
6 6 01
7 7 06

Now, I want to see this result set(well, I actually want more fields,
this is just to give you an idea)

MESSAGE_ID SUBJECT AvgRating Parent_ID
1 Very valuable message 9 0
4 Awful message 1 0

Now, if I want to see the responses to Message_ID 1 then running the
query I'll see this result:

MESSAGE_ID SUBJECT AvgRating Parent_ID
2 Not very valuable 7 1
response, still ok tho
3 Awful response 1 1

If I then query for the Message_ID 4 I'll see this:

MESSAGE_ID SUBJECT AvgRating Parent_ID
5 Very valuable response 9 4
7 Not very valuable 7 4
response, still ok tho
6 Awful response 1 4

Do I make sense? :D

Cheers,

Andrew