Thread: Getting rows in a very specific order

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?

> 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?

If you've got the intarray contrib module installed, you can use

idx(array[1,31,68,97], i)

On Mon, Sep 8, 2008 at 12:29 PM, Gordon <gordon.mcvey@ntlworld.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

Gordon-

without disclosing company proprietary specifics can you provide a pared down schema of the affected
tables and their column names
e.g.
Table1
{
 column1 int primary key;
};

Table2
{
 int column2;
 int column1 references table1(column1)
}

thanks
Martin
_____________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> From: gordon.mcvey@ntlworld.com
> Subject: [GENERAL] Getting rows in a very specific order
> Date: Mon, 8 Sep 2008 09:29:44 -0700
> To: pgsql-general@postgresql.org
>
> 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?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now