Thread: Sorting by parent/child relationships

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;

Any advice will be greatly appreciated.

thanks!

eric

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

> 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

Thanks Jochem!  This is almost exactly what I needed, and it's much
cleaner than the ugly monster I started with!  My biggest problem was
that parent rows had a parent_id of -1, when they needed a parent_id of
collection_id to sort correctly.

eric