Re: table with sparce data

From: Vadim Tropashko <vadimtro_at_yahoo.com>
Date: 22 Aug 2003 17:48:32 -0700
Message-ID: <22d2e427.0308221648.aea080a@posting.google.com>

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<9vt1b.18$3j7.31_at_news.oracle.com>...
> Any simple analytical solution?

Here is analytical solution:

SQL> select rn, comm from (
  2 select ename, comm

  3             ,count(1) over (order by ename) rn
  4             ,lead(comm) over (order by ename) lead
  5      from commissions

  6 order by ename
  7 ) where comm-lead <> 0 or comm-lead is null   8 ;

        RN COMM
---------- ----------

         1          0
         2        300
         8          0
         9       1400
        13          0
        14        500

Then, all you need to do is subtract RN-LAG(RN) to get the count. Received on Fri Aug 22 2003 - 19:48:32 CDT