Oracle - Convert value from rows into ranges

Are there any techniques that would allow a row set like this

WITH 
base AS
(
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL

    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL

    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL

    SELECT 21 N FROM DUAL
)
SELECT  a.N
FROM base a

to yield results

 1     3
 6     7
17    19
21    21

It is in effect a rows to ranges operation. 开发者_StackOverflow社区I'm playing in Oracle Land, and would appreciate any suggestions.

I feel like this can probably be improved on, but it works:

WITH base AS  (
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL
    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL
    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL
    SELECT 21 N FROM DUAL
)
, lagged AS
(
    SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
    SELECT n, row_number() OVER (ORDER BY n) groupnum
      FROM lagged
      WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
    SELECT n, (SELECT MAX(groupnum) FROM groups
                 WHERE groups.n <= base.n
              ) groupnum
      FROM base
)
SELECT groupnum, MIN(n), MAX(n)
  FROM grouped
  GROUP BY groupnum
  ORDER BY groupnum

Another way:

WITH base AS  
(
    SELECT  1 N FROM DUAL UNION ALL
    SELECT  2 N FROM DUAL UNION ALL
    SELECT  3 N FROM DUAL UNION ALL
    SELECT  6 N FROM DUAL UNION ALL
    SELECT  7 N FROM DUAL UNION ALL
    SELECT 17 N FROM DUAL UNION ALL
    SELECT 18 N FROM DUAL UNION ALL
    SELECT 19 N FROM DUAL UNION ALL
    SELECT 21 N FROM DUAL
)
select min(n), max(n) from 
  (
    select n, connect_by_root n root from base
    connect by prior n = n-1
    start with n not in (select n from base b 
                         where exists (select 1 from base b1 where b1.n = b.n-1)
                        )
  ) 
  group by root
  order by root

Yet another way:

with base as (
    select  1 n from dual union all
    select  2 n from dual union all
    select  3 n from dual union all
    select  6 n from dual union all
    select  7 n from dual union all
    select 17 n from dual union all
    select 18 n from dual union all
    select 19 n from dual union all
    select 21 n from dual)
select a,b 
from (select a
            ,case when b is not null and a is not null
                  then b
                  else lead(n) over (order by n)
            end b
      from (select n
                  ,a
                  ,b
            from (select n
                        ,case n-1 when lag (n) over (order by n) then null else n end a
                        ,case n+1 when lead (n) over (order by n) then null else n end b
                  from base)
      where a is not null
         or b is not null))
where a is not null
order by a