Re: Order of selected rows.

Erika,

Kai was nearly there, but the hierarchical query would only guarantee the correct order if you data contained the ordering.

This mean altering the region table to include an order column. The order can be obtain from the following ;
> > > SELECT rownum ord_value, lpad(' ',2*(LEVEL-1))||name
> > > FROM region
> > > START WITH name= 'IR1'
> > > CONNECT BY PRIOR name=parent;

Chris.

Erika Grondzakova wrote:
>
> Hi Kai,
>
> Kai Horstmann wrote:
> >
> > Erika Grondzakova schrieb:
> > >
> > > Could I rely on that when I run several times the SELECT statement of
> > > the HIERARCHICAL query, I will receive the same order of the selected
> > > rows?
> > > Especially I'm interested in the same order of the rows on the same
> > > level.
> > >
> > > Example :
> > >
> > > SELECT lpad(' ',2*(LEVEL-1))||name
> > > FROM region
> > > START WITH name= 'IR1'
> > > CONNECT BY PRIOR name=parent;
> > >
> >
> > Try :
> >
> > select * from
> > ( SELECT lpad(' ',2*(LEVEL-1))||name as my_logical_column
> > FROM region
> > START WITH name= 'IR1'
> > CONNECT BY PRIOR name=parent ) a
> > order by a.my_logical_column;
> >
> > The result of the complete subquery is sorted in a second run.
> >
> > Hpe that helps
> > Kai
>
> Thanks, but it is not what I wanted. With your select I received the
> list ordered by the column NAME like following :
>
> C10L1
> C10R1
> C11L1
> C11R1
> ....
> C8L1
> C8R1
> C9L1
> C9R1
> DSL1
> DSR1
> LSS1
> IR1
>
> But I would like to receive list of HIERARCHICAL ORDER (with the proper
> levels and sublevels) and be sure that this order will be the same the
> next time when I execute the SELECT statement. So the result should be
> like this...
>
> IR1
> DSL1
> C11L1 |
> C10L1 | to be sure that these rows will be always
> C9L1 | in this order when I execute SELECT next time...
> C8L1 |
> DSR1
> C8R1
> C9R1
> C10R1
> C11R1
> LSS1
> C1R1
> C2R1
> C3R1
> ....
> C2L1
> C1L1
> C6R1
>
> Exactly I don't need to order rows by the column NAME, what I only need
> to be sure that the order of selected rows will be always the same when
> I execute it again.
>
> Thank you for your advise or any comments.
>
> Best regard,
>
> Erika Grondzakova
Received on Wed Jan 27 1999 - 14:09:26 CST