Query conditional ordering in Mysql

Question

I have a table called comentarios, formed by the columns: id, descricao, criado_em and comentario_pai, how it is possible to see in the photo.

I need to create a query, in which the answers are printed after your comment (comentario_pai), example:

1. Comment 1
2. Answer 1
3. Answer 3
4. Comment 2
5. Answer 2

I tried to build some querys using order by, but I was not successful.

Answer 1

The problem is the null in the field comentario_pai.

One of the ways to solve it is by using the COALESCE:

 select * from `comentarios` order by coalesce(comentario_pai, id);

See how it works on sql Fiddle

---

COALESCE is a function that is part of the specification SQL-92. All database engines that comply with this specification support this function.

What COALESCE ago?

Returns the first Non-null element of the list passed as argument of the function. If all elements are Null then the result is Null.

Example:

Select coalesce(campo1,campo2,'N.D.')

If campo1 is not Null returns the campo1, if not, evaluate the content of campo2. If campo2 is not Null returns the campo2, if not, come back 'N.D.'.

Here is the internal structure of the function coalesce:

COALESCE(value1, value2, value3, ...)

is equivalent to:

CASE WHEN value1 IS NOT NULL THEN value1
     WHEN value2 IS NOT NULL THEN value2
     WHEN value3 IS NOT NULL THEN value3
     ...
     END

Answer 2

Create a field that always has the main value. As in the example below and sort by it.

Select *, 
     CASE WHEN comentario_pai is null THEN id
     ELSE comentario_pai END Ordem
FROM Comentarios
Order By           
     CASE WHEN comentario_pai is null THEN id
     ELSE comentario_pai END