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?
ps: i've found that DISTINCT clause removes the duplicates, but doesn't keep the right order.