Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: eliminating gaps

Re: eliminating gaps

From: Dieter Noeth <dnoeth_at_gmx.de>
Date: Thu, 31 Aug 2006 14:29:23 +0200
Message-ID: <ed6knq$4p3$1@online.de>


eokumus79_at_gmail.com wrote:

> A B
> 1 10000
> 1 10001
> 1 10002
> 1 10006
> 1 10007
> 2 10008
> 2 10009
> 2 10010
> 2 10011
> 2 10012
> 2 10015
> 3 10021
> 3 10022
> 3 10023
> 3 10031
> 3 10032
> 3 10033
> 3 10034
> 3 10035
> 4 10041
> 4 10042
> 4 10044
>
> i want to get following output
>
> A B C
> 1 10000 10002
> 1 10006 10007
> 2 10008 10015
> 3 10021 10023
> 3 10031 10035
> 4 10041 10042
> 4 10044 10044

select

   a,
   min(b),
   max(b)
from
  (
   select

     a - rank() over (partition by A order by b asc) as grp,
     a

   from mytab
  ) dt
group by a, grp
order by 1,2;

If a/b is not unique, replace rank with dense_rank...

Dieter Received on Thu Aug 31 2006 - 07:29:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US