SQL Sorting of child elements based on parent elements

When you have questions and answers (for example) on your site, you want to sort and show answers under questions. My solution is based on few assumptions:

  • Questions and Answers are stored in the same SQL table
  • Questions and Answers IDs are chronological
  • Difference between a question and an answer is based on id_parent column
  • Question's id_parent is zero
  • Answer's id_parent is non-zero

Now normally if you sort by ID then you may end up with something like:

  • Question 1
  • Question 2
  • Answer to question 1
  • Question 3
  • Answer to question 2
  • Answer to question 1
  • Answer to question 3

But in fact we want:

  • Question 1
  • Answer to question 1
  • Answer to question 1
  • Question 2
  • Answer to question 2
  • Question 3
  • Answer to question 3

The solution is very simple - use new select column which will:

  • Keep id_parent if it is non-zero
  • Set id_parent as ID if it is zero

SQL query that will return questions and answers to them in right order is:

SELECT *, IF(`id_parent`>0,`id_parent`,`id_comment`) AS `id_super`
FROM `comments`
ORDER BY `id_super` ASC, `ID` ASC