Hi.

I'm writing a forum-like site, which has only one table for both posts and replies.

id INT(5), title, parent INT(5), for example.

If the user post a new topic, the id is auto-incremented and parent is 0.
If the user replies to an existent topic, id is 0 and parent is the id of the main topic.

now the question:

I'm trying to find a query that returns ONLY the main posts (id=0), without the replies. BUT it must be in order of updated items. If I post a new topic, it must be returned first in the query. If I post a reply to an old topic, this topic must comes first.

the code that I'm trying is:

SELECT DISTINCT parent.* FROM table AS parent
LEFT JOIN table AS son ON son.sub=parent.id
WHERE ((son.sub=parent.id OR parent.sub=0) AND parent.tipo=$p_id)
ORDER by son.data DESC, parent.data DESC

it doesn't work very well. there are such cases in that the last item inserted doesn't come first. i didn't find the error nor the logic of the values returned.

can someone help me on this?
thanks

ps: i've found that DISTINCT clause removes the duplicates, but doesn't keep the right order.