Sorting by parent/child relationships

Поиск
Список
Период
Сортировка
От Eric Ridge
Тема Sorting by parent/child relationships
Дата
Msg-id D3ADE25911614840BC69C72E3171E4ED028110@tcdiexch.tcdi.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Mihai Gheorghiu"
Дата:
Сообщение: Re: Problem in installing Postgresql-7.1.2
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Why do I get a circular query when I insert tuples with this rule ?