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…
Advertisement
Answer
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;