Re: nice sql problem

From: Ronald <devnull_at_ronr.nl>
Date: 30 Jan 2002 04:01:03 -0800
Message-ID: <67ce88e7.0201300401.75f2db40@posting.google.com>

mike2322_at_hotmail.com (Mike Liu) 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

<snip>
> 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
> /

I think this is also working and also in others than oracle: select min(aa.a),min(bb.b)
from
(
select a,0 b, rownum rx
from z a
where not exists (select 'x'

                 from z b
                 where b.b = a.a)

union all
select 0, b, rownum
from z a
where not exists (select 'x'

                 from z b
                 where a.b = b.a) ) aa,

(
select a,0 b, rownum rx
from z a
where not exists (select 'x'

                 from z b
                 where b.b = a.a)

union all
select 0, b, rownum
from z a
where not exists (select 'x'

                 from z b
                 where a.b = b.a) ) bb

where aa.a < bb.b
and aa.a > 0
group by aa.rx
order by 1,2
/

It's not as elegant but it works - I think Needs an index on a and on b for optimal performance and can still be improved.

Ronald.

---

http://ronr.nl/unix-dba Received on Wed Jan 30 2002 - 06:01:03 CST