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;