Re: Sorting by parent/child relationships

Eric Ridge wrote:

> This may have been asked/answered a million times already, but what the
> heck...
>
> Basically, I have a table that maintains parent<-->child relationships
> within itself. The parent_id field points to the collection_id field.
> A parent_id id of -1 means it's a root record (ie, no parent). Pretty
> simple.
>
> Question is, how do I sort a query so that children follow their parent?
>
> I came up with this, and it works, but I'm sure there's a better way:
>
> SELECT *, CASE WHEN parent_id = -1 THEN collection_id||'' WHEN parent_id
> != -1 THEN parent_id||collection_id END as z FROM collection order by z;

Some self join would work best I suppose:

select p.*, c.*
from collection p, collection.c
where c.parent_id = p.collectionid
order by p.collectionid ASC, c.collectionid

Depending on your datset you might need to use an outer join instead.

Jochem