The logic is that each new record will "inherit" the parent id from above, therefore when the select all is done, it should be positioned below its parent.
The easiest solution to this problem depends on how many parent-child levels you allow. If there can only be one level, that is, there can never be "grandparents", then the order can be done like this:
SELECT *
FROM la_tabla
ORDER BY case when padre is null then id else padre end, -- agrupa hijos con padres
padre is not null, -- hijos después de padres
id -- hijos en orden de ID
... where with the expression CASEthe children are sorted using the IDparent's so that they are grouped together.
But if it is possible to have multiple levels of parent-child, then it is necessary to use a recursive query. This would be an example (inspired from this answer ):
with recursive cte as (
select id, contenido, padre, array[id] as path
from la_tabla
where padre is null
union all
select t.id, t.contenido, t.padre, c.path || t.id as path
from cte c
join la_tabla t
on t.padre = c.id
)
select id, contenido, padre
from cte
order by path
I imagine that you mean ordering from greatest to least, with 1 being the "greatest" for having one unlike the others that are null. You could try to use DESC
what happens is that the null values cannot be sorted by the ORDER BY.
I suggest you try giving it a value for when they are null, like so:
The "WHEN" will evaluate that if it is null it puts an "empty" string that can be sorted.
OP's comment:
The easiest solution to this problem depends on how many parent-child levels you allow. If there can only be one level, that is, there can never be "grandparents", then the order can be done like this:
... where with the expression
CASE
the children are sorted using theID
parent's so that they are grouped together.But if it is possible to have multiple levels of parent-child, then it is necessary to use a recursive query. This would be an example (inspired from this answer ):
I imagine that you mean ordering from greatest to least, with 1 being the "greatest" for having one unlike the others that are null. You could try to use DESC
So:
I hope it helps you!