Re: Getting rows in a very specific order

On Mon, Sep 8, 2008 at 12:29 PM, Gordon <gordon(dot)mcvey(at)ntlworld(dot)com> wrote:
> I'm considering using an array of ints column in a table which lists a
> row's ancestry. For example, if item 97 is contained within itme 68
> and that item is contained with in 31 and that item is contained
> within item 1 then the value of the hierachy column would be
> {1,31,68,97}, the numbers refer to the primary keys of the rows.
>
> If I use the value of the hierarchy column in a query I can get all
> the rows that a given row is an descendant of. (SELECT * FROM items
> WHERE itm_id IN (1,31,68,97), for example. However, I need the rows
> to be in the correct order, ie the root node first, child second,

convert that to

select * from items where itm_id = 1
union all
select * from items where itm_id = 31
...

If this is a recursive table you should probably thinking about
writing recursive functions that access the structure or possibly use
a materialized path approach.

merlin