Sorting by parent/child relationships

From: "Eric Ridge" <ebr(at)tcdi(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Sorting by parent/child relationships
Date: 2001-10-16 08:01:40
Message-ID: D3ADE25911614840BC69C72E3171E4ED028110@tcdiexch.tcdi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Randal L. Schwartz 2001-10-16 08:22:45 Re: "Relation x does not exist" error when x does exist
Previous Message Alessio Bragadini 2001-10-16 07:43:36 Re: retriving views name