Re: nice sql problem - an ugly heavy solution

From: Achille Carette <acarette_nospam_at_aprico-consult.com>
Date: Tue, 29 Jan 2002 17:45:55 +0100
Message-ID: <3c56d21f$0$33513$ba620e4c@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 - 10:45:55 CST