Oracle: Analytical functions Sub totals after each change in value

Question

I have the following data (order of records as in the example):

A   B
1   10
1   20
1   30
1   40
2   50
2   65
2   75
1   89
1   100

from SQL:

with x as (
  select A, B
  from (
    select 1 as A, 10 as B from dual
    union all
    select 1 as A, 20 as B from dual
    union all
    select 1 as A, 30 as B from dual
    union all
    select 1 as A, 40 as B from dual
    union all
    select 2 as A, 50 as B from dual
    union all
    select 2 as A, 65 as B from dual
    union all
    select 2 as A, 75 as B from dual
    union all
    select 1 as A, 89 as B from dual
    union all
    select 1 as A, 100 as B from dual
  ) 
)
select A, B
from X

I want to group the data for each change of value in column A, I want to get the following result:

A   MIN(B)  MAX(B) 
1   10      40
2   50      75
1   89      100

How to get such a result in the ORACLE 11. I would expect a simple implementation...

Answer 1

This is a gaps and islands problem, solved using row_number analytic function

SELECT a,
       MIN(b),
       MAX(b)
FROM (
     SELECT x.*,
            ROW_NUMBER() OVER(
                 ORDER BY b
            ) - ROW_NUMBER() OVER(
                 PARTITION BY a
                 ORDER BY b
            ) AS seq
     FROM x
)
GROUP BY a,
         seq;  

Demo