I have a table named comentarios , formed by columns: id , descricao , criado_em and comentario_pai , as you can see in the photo.
I need to create a query, in which the answers are printed after your comment ( comentario_pai ), example:
I tried to build some querys using order by, but I did not succeed.
The problem is the null in the comentario_pai field.
One way to resolve this is to use COALESCE :
select * from 'comentarios' order by coalesce(comentario_pai, id);
See how it works in sql Fiddle
COALESCE is a function that is part of the SQL-92 . All database engines that meet this specification support this function.
What does COALESCE do?
Returns the first non-null element of the list passed as the function's argument. If all elements are Null then the result is Null.
Example:
Select coalesce(campo1,campo2,'N.D.')
If campo1 is not Null, return the campo1 , otherwise, evaluate the contents of campo2 .
If campo2 is not Null return campo2 , otherwise, return '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
Create a field that always has the primary value. Like 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