SQL & PL/SQL

The system I'm working on allows you to create various relationships between items, whether they are hitched to one another, stacked on top, put into, etc...

The table is storing the parent information in line with the child. The real short version of it is:
GEARTABLE(
INTERNAL_ID,
ITEM_ID,
SERIAL_NUMBER,
DESCRIPTION,
PARENT_ITEM_ID,
PARENT_SERIAL_NUMBER,
ASSOCIATION)

(I know instead of re-storing the parent item id and serial number I could store the internal_id, but I'm converting this from a previous system and haven't made the adjustment yet. If it would help me towards what I need I'll definately change it now vs. later.)

I'm using a heirarchical query with success but the client wants to be able to view the data in whichever sort order they want. With the CONNECT BY, you can only sort the sibling info, which is almost the exact opposite of what I need...and I know that doing an order by on the whole thing destroys the ordered heirarchy result.

Looking at the output, my parents are coming out in the order they were inserted (looking at ROWID). What I need to be able to do is sort on any field in the column list but have it do that sort per level. In other words, each row has an item_id and a serial number. I need to be able to sort by the item_id OR the serial number but still retain the heirarchy. So if a user says sort by serial number, the first level (parents & orphans) gets ordered by serial number, then level 2 (still retaining the heirarchy) is then ordered by serial number and so on through the tree.

Is there any workaround for this? CONNECT BY is a real nice feature, but if you can't order the top level....how helpful is it really? The only things I can think of are either creating a monstrous stored procedure to reorganize all the info based on user requested order, or possibly take the data they need to work with, dump it into a global temp table in the correctly sorted order they need it in and THEN do the CONNECT BY on that.

Message was edited by:
user526667