I have a MySQL database table where I save the feedback (column 'feed') from users.
They can both ask questions (type = que, with ref = 0) and reply (type = ans) to the posted questions.
The 'ref' column shows the 'id' of the question they refer to:
id username feed type ref
1 mr_x question_1 que 0
2 mrs_y question_2 que 0
3 mr_z answer_to_2 ans 2
4 mr_z answer_to_1 ans 1
5 mr_x answer_to_2 ans 2
"SELECT * FROM feedback ORDER BY ref ASC";
mr_x question_1 que 0
mr_z answer_to_1 ans 1
mrs_y question_2 que 0
mr_z answer_to_2 ans 2
mr_x answer_to_2 ans 2
The following query will return the records from the
feedback table ordered by the parent
id, with the record from the parent thread appearing before the children.
SELECT f.* FROM feedback f ORDER BY CASE WHEN f.ref = 0 THEN f.id ELSE f.ref END, f.id
ORDER BY uses two criteria for ordering. First, it orders by the
id, when the
ref be zero (indicating a parent thread), or the
ref when it is not zero, indicating a child thread. Then, to place the parent thread before the children, it orders next by the
id. This works, because a parent thread will always have an
id which is less than its children.