CODEWITHSUNDEEP
oracle-databasegaps-and-islands
Maxim Mandrik
Maxim Mandrik

Reputation: 447

Select ranges (min, max) only for a sequence of numbers +1

Select ranges (min, max) only for a sequence of numbers +1

There is a table:

col_number
1
2
3
4
9
10
12

It is necessary to obtain data so:

min   max
1     4
9     10
12    12

Upvotes: 0

Views: 222

Answers (4)

user5683823
user5683823

Reputation:

In Oracle version 12.1 or later, you can use match_recognize like so:

with
  my_table(col_number) as (
    select  1 from dual union all
    select  2 from dual union all
    select  3 from dual union all
    select  4 from dual union all
    select  9 from dual union all
    select 10 from dual union all
    select 12 from dual
  )
-- end of sample data (for testing only, not part of the solution)
select min_number, max_number
from   my_table
match_recognize(
  order by col_number
  measures first(col_number) as min_number, last(col_number) as max_number
  pattern  (a b*)
  define b as col_number = prev(col_number) + 1
);

MIN_NUMBER MAX_NUMBER
---------- ----------
         1          4
         9         10
        12         12

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In Oracle, you can actually do this without window functions:

select min(col) as "min", max(col) as "max"
from (select t.*, rownum as seqnum
      from (select t.* from t order by col) t
     ) t
group by (col - seqnum)
order by min(col)

Upvotes: 2

forpas
forpas

Reputation: 164054

Without window functions:

WITH 
nmin AS (
  SELECT col_number FROM numbers t WHERE NOT EXISTS 
    (SELECT col_number FROM numbers WHERE numbers.col_number = t.col_number - 1)),
nmax AS (
  SELECT col_number FROM numbers t WHERE NOT EXISTS 
    (SELECT col_number FROM numbers WHERE numbers.col_number = t.col_number + 1))
SELECT 
  nmin.col_number minnumber, 
  (SELECT MIN(nmax.col_number) FROM nmax WHERE nmax.col_number >= nmin.col_number) maxnumber
FROM nmin
ORDER BY nmin.col_number;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

This is called a gaps & islands problem. You solve this by subtracting the row number from the value to get group keys:

select min(col), max(col)
from
(
  select
    col,
    col - row_number() over (order by col) as grp
  from mytable
) 
group by grp
order by min(col);

Upvotes: 2

Related Questions