Re: nice sql problem

Isn't it always a bad idea to write queries ( as applications ) based on too much assumptions, like " in 'this' case, it works" ? Today it works. Tomorrow, a segond leaf node is added to one hierarchy only, and you have a bug in your application.

"In this case, it works" is certainly not a reason why a solution is good or not - this condition is necessary, but not sufficient. IM(modest)O this little phrase is a good way to write code which is not robust.

Could have written too ( but do not even think to insert a new row in the table ):
select a, decode(length(b), 1, a+3, 2, b) b from z where a in (1, 7, 12);

"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
> >
> > "Mike Liu" <mike2322_at_hotmail.com> 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
> > > > 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 Wed Jan 30 2002 - 06:14:30 CST