Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: nice sql problem
"Achille Carette" <acarette_nospam_at_aprico-consult.com> wrote in message news:<3c57af8d$0$33510$ba620e4c_at_news.skynet.be>...
> 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
It's just trick for the specific dataset. In general, it's much easier to do in a procedure. In oracle 8.1.6 and above, this probably works,
select * from
(
select first_value(a) over (partition by gp order by rn) as a, b
from
(
select a, b, rn, sum(gp) over (order by rn) as gp from ( select a, b, rownum rn, decode(level, 1, 1, 0) gp from z connect by a = prior b start with a not in ( select b from z) )
Thanks,
Mike
Received on Thu Jan 31 2002 - 16:10:06 CST