Re: nice sql problem

From: Ronald <devnull_at_ronr.nl>
Date: 1 Feb 2002 08:25:32 -0800
Message-ID: <67ce88e7.0202010825.d68e628@posting.google.com>

mike2322_at_hotmail.com (Mike Liu) wrote in message news:<2262aa8e.0201311410.69537c52_at_posting.google.com>...

> "Achille Carette" <acarette_nospam_at_aprico-consult.com> wrote in message news:<3c57af8d$0$33510$ba620e4c_at_news.skynet.be>...

> > 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 ).
> > > > 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
> > > 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

> 
> It's just trick for the specific dataset. In general, it's much easier
> to do in a procedure. In oracle 8.1.6 and above, this probably works,
> 
> select * from 
> (
>    select first_value(a) over (partition by gp order by rn) as a, b
>    from 
>    (
>      select a, b, rn, sum(gp) over (order by rn) as gp
>      from (
>        select a, b, rownum rn, decode(level, 1, 1, 0) gp
>        from z
>        connect by a = prior b
>        start with a not in ( select b from z)
>      )
>    )
> )
> where b not in (select a from z)

Nice query Mike. I also got this one and this one will run in most sql databases I think: no connect by and no rownums needed.

SELECT MA1.A, MB1.B
  FROM (SELECT COUNT(*) MAC, Z1.A

          FROM
             (SELECT Z1.A
                FROM Z Z1
               WHERE NOT EXISTS (SELECT 1
                                   FROM Z Z3
                                  WHERE Z1.A = Z3.B)) Z1
            ,(SELECT Z1.A
                FROM Z Z1
               WHERE NOT EXISTS (SELECT 1
                                   FROM Z Z3
                                  WHERE Z1.A = Z3.B)) Z2
         WHERE Z1.A >= Z2.A
         GROUP BY Z1.A) MA1
     , (SELECT COUNT(*) MBC, Z1.B
          FROM
             (SELECT Z1.B
                FROM Z Z1
               WHERE NOT EXISTS (SELECT 1
                                   FROM Z Z3
                                  WHERE Z1.B = Z3.A)) Z1
            ,(SELECT Z1.B
                FROM Z Z1
               WHERE NOT EXISTS (SELECT 1
                                   FROM Z Z3
                                  WHERE Z1.B = Z3.A)) Z2
         WHERE Z1.B >= Z2.B
         GROUP BY Z1.B) MB1

 WHERE MA1.MAC = MB1.MBC
 ORDER BY 1, 2
/
(got it from a db2 dba).

Ronald.

---

http://ronr.nl/unix-dba Received on Fri Feb 01 2002 - 10:25:32 CST