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 - a better solution

Re: nice sql problem - a better solution

From: Achille Carette <acarette_nospam_at_aprico-consult.com>
Date: Tue, 29 Jan 2002 18:41:17 +0100
Message-ID: <3c56df18$0$33499$ba620e4c@news.skynet.be>


The problem with this solution is that using the max() function will return only one leaf row for each root row. I enhanced it, doing a join between a query giving all leaf rows, and a second one giving the complete path to this leaf row ( using sys_connect_by_path() ) :

select
 substr (x.s, 2, instr(x.s,',',2)-2 ) a,  x.b
from
(
  select
    a, b, level l, substr(sys_connect_by_path(a,','),1,10)||',' s   from
    z
  connect by prior b = a
  start with a not in ( select b from z ) ) x
inner join
(
  select b from z where b not in ( select a from z ) ) y
on x.b = y.b
;

A lot better... but could certainly be further enhanced...



Achille Carette
Senior Software Engineer
Enterprise Java-based Architectures
Oracle DBA
acarette_at_aprico-consult.com
+32 497 533 802

"Achille Carette" <acarette_nospam_at_aprico-consult.com> wrote in message news:3c56d21f$0$33513$ba620e4c_at_news.skynet.be...
> If i'm not wrong, i found a solution. But i'm quite unsatisfied. This
> solution is fairly heavy, and there should be a simpler way to do it - in
a
> few lines probably. Moreover, i tested this on a 9i database, and i don't
> know if the sys_connect_by_path() exists in previous versions.
>
> My solution is to use the sys_connect_by_path() oracle function to get the
> first element of the hierarchy for each node. The following gives first
and
> last elements of each hierarchy, with the level of the last element:
>
> select
> l, b,
> substr (s, 1, instr(s,',')-1 ) s
> from
> (
> select
> level l,
> b,
> substr(sys_connect_by_path(a,',')||',',2) s
> from z
> connect by prior b = a
> start with a not in ( select b from z )
> )
>
> And the following gives only the highest level rows for each root node :
>
> select
> s, max(l) l
> from
> (
> select
> l, b,
> substr (s, 1, instr(s,',')-1 ) s
> from
> (
> select
> level l,
> b,
> substr(sys_connect_by_path(a,',')||',',2) s
> from z
> connect by prior b = a
> start with a not in ( select b from z )
> )
> )
> group by s
>
> Joining the two on s and l does the work. But the full query is really
ugly
> ( do not even think to debug it directly ).
>
> select
> vx.l, vx.b,
> substr(vy.s,1,10) s
> from
> (
> select
> l, b,
> substr (s, 1, instr(s,',')-1 ) s
> from
> (
> select
> level l,
> b,
> substr(sys_connect_by_path(a,',')||',',2) s
> from z
> connect by prior b = a
> start with a not in ( select b from z )
> )
> ) vx
> inner join
> (
> select
> s, max(l) l
> from
> (
> select
> l, b,
> substr (s, 1, instr(s,',')-1 ) s
> from
> (
> select
> level l,
> b,
> substr(sys_connect_by_path(a,',')||',',2) s
> from z
> connect by prior b = a
> start with a not in ( select b from z )
> )
> )
> group by s
> ) vy
> on vx.l=vy.l and vx.s=vy.s
> ;
>
> ------------
> Achille Carette
> Senior Software Engineer
> Enterprise Java-based Architectures
> Oracle DBA
> acarette_at_aprico-consult.com
> +32 497 533 802
>
>
> "Ronald" <devnull_at_ronr.nl> 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
>
>
Received on Tue Jan 29 2002 - 11:41:17 CST

Original text of this message

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