Help building query (grouping etc.)

[Alkonaut]

I'm doing a simple forum for a website.
In the list of messages, the relevant fields are, id and parent.

For new threads, the parent column is set to -1, and for replies, the parent column is holding the id of the message replied to (simple, eh?).

The problem now is when I want to make a query for the "freshest" threads. For example this query:

SELECT MAX(id),parent FROM messages
GROUP BY parent
ORDER BY 1 DESC
LIMIT 0,3

Will give me the freshest threads. HOWEVER, it will only allow for ONE new thread in this list (since I will group all -1 parents to one.) If the three newest messages in the forum are thread-roots, it will display only the newest, and 2 threads that have fresh replies. This is not right, the 3 thread-root messages are newer.

I guess what I want to do is the above, but leave all root messages (parent < 0) ungrouped, and group all rows on parent where parent is > 0.

Are such conditional groupings possible? Is there another simple solution I didn't think of?

Thanks
/A

 

[Alkonaut]

Fixed it!

No help needed, thanks anyway. Pretty ugly query in the end...but i'll post it for laughs

------------------------------------------------

SELECT forummessages.id, forummessages.subject, forums.readaccess, COALESCE(MAX(replies.id), MAX(forummessages.id))

FROM forummessages LEFT OUTER JOIN forummessages AS replies ON forummessages.id = replies.parent INNER JOIN forums ON forums.id = forummessages.forum

WHERE forummessages.parent=-1
GROUP BY forummessages.id
HAVING forums.readaccess <= ?

ORDER BY 4 DESC
LIMIT 0,3
-----------------------------------------------------