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,
grandchild third etc. I'm guessing the order can be set with an ORDER
BY, but I've only ever used ORDER BY to order by ascending or
descending order of a single column. Is there a way to retrieve the
rows explicitly in the order they are listed in the hierarchy array?