Re: nice sql problem

From: Ronald <devnull_at_ronr.nl>
Date: 30 Jan 2002 05:57:49 -0800
Message-ID: <67ce88e7.0201300557.e84d543@posting.google.com>

Dimitry <dimonvv_at_ua.fm> wrote in message news:<3C57D935.D7C10116_at_ua.fm>...
> In this case it works!
> Best regards,
> Dimitry.
> Achille Carette пишет:
> > 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

That's wrong data so it's not a problem ;-) In the meantime I have been playing with it a bit more. Provided the data is correct this also works:
select min(a),max(a)
from
(
select a, rownum rx
from z a
where not exists (select 'x'

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

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

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

group by rx
/

and has a reasonable performance when a and b have indexes. Don't know about the difference in a real life table yet but the stats in a test table look better compared to the connect by method. Real problem is: it should also work in a db2 v5 database. (no connect by, no rownum ...)

Ronald.

---

http://ronr.nl/unix-dba Received on Wed Jan 30 2002 - 07:57:49 CST