Re: nice sql problem

In this case it works!

Best regards,
Dimitry.

Achille Carette пишет:

> I'm afraid this won't work in all cases.
>
> This could only work if, for all rows, child row.a > parent row.a ( if not,
> using min() won't retrive the root value ).
>
> And what if, in your hierarchy, u have two leaf rows of different levels for
> the same root row ? Like :
> 1 2
> 2 3
> 2 4
> 4 5
>
> "Mike Liu" <mike2322_at_hotmail.com> wrote in message
> news:2262aa8e.0201291122.6ab233fd_at_posting.google.com...
> > devnull_at_ronr.nl (Ronald) wrote in message
> news:<67ce88e7.0201290442.2bd6ad09_at_posting.google.com>...
> > > given table contains
> > > select * from z;
> > > A B
> > > ---------- ----------
> > > 1 2
> > > 2 3
> > > 3 4
> > > 7 8
> > > 8 10
> > > 12 13
> > >
> > > wanted output should contain
> > > A B
> > > ---------- ----------
> > > 1 4
> > > 7 10
> > > 12 13
> > >
> > > how ? in plain sql - no plsql.
> > >
> > > select min(a) a, max(b) b
> > > from z
> > > connect by prior b = a
> > > start with a = 1
> > > /
> > > A B
> > > ---------- ----------
> > > 1 4
> > >
> > > is not complete. Any suggestions ?
> > >
> > > tnx,
> > > Ronald.
> > > -----------------------
> > > http://ronr.nl/unix-dba
> >
> > Try this,
> >
> > select min(a), max(b)
> > from (
> > select a, b, rownum-level gp
> > from z
> > connect by a = prior b
> > start with a not in (
> > select b from z
> > )
> > )
> > group by gp
> > /
> >
> > hth,
> > Mike
Received on Wed Jan 30 2002 - 05:29:57 CST