Re: HELP sorting a table before hierarchical query

If you use an ORDER BY, you will of course loose the hierarchy. It might work by forcing Oracle to use an index.

Try:

create index concept_sort on tables.concept_hierarchy(parent_concept, current_concept) -- order of columns depends on what you want /
select /*+ INDEX(tables.concept_hierarchy concept_sort) */

   lpad('&nbsp',5*(level-1))||current_concept,parent_concept from tables.concept_hierarchy
start with parent_concept='Concepts'
connect by prior current_concept=parent_concept;

Marc

BPoulin wrote in message <7pujhk$hnu$1_at_pulp.ucs.ualberta.ca>...
>Is there any way I can sort a table (short of completely recreating
>it,unless there is a -good- way of automating that) so that a hierarchical
>query will retrieve the information in alphabetical order (within each
>subgroup of the hierarchy)?
>
>The query will be
>
>select lpad('&nbsp',5*(level-1))||current_concept,parent_concept
>from tables.concept_hierarchy
>start with parent_concept='Concepts'
>connect by prior current_concept=parent_concept;
>
>This worked fine and was easy to read when I created the table using
'create
>... as select ... order by' but after any insertions into the structure the
>ordering is lost. I've tried to copy all the records to another table,
>sorting them as I go, and then copying them back(again with 'order by') but
>the order is still lost. I've tried this in SQL*PLUS and with a cursor in a
>procedure. I've also tried using a view to sort the records before doing
the
>hierarchical query but this is not supported.
>
>The readability of the results of this query is crucial so I have to do
this
>somehow. I'm hoping I'm not going to have to recreate the table every time
I
>insert something. This will be output to a web page.
>
>Thanks in advance.
>
>
Received on Wed Aug 25 1999 - 00:22:39 CDT