Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: nice sql problem

Re: nice sql problem

From: Achille Carette <acarette_nospam_at_aprico-consult.com>
Date: Wed, 30 Jan 2002 09:30:45 +0100
Message-ID: <3c57af8d$0$33510$ba620e4c@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
Received on Wed Jan 30 2002 - 02:30:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US