Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: eliminating gaps
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
If a/b is not unique, replace rank with dense_rank...
Dieter Received on Thu Aug 31 2006 - 07:29:23 CDT