Re: nice sql problem

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 Tue Jan 29 2002 - 13:22:27 CST