CODEWITHSUNDEEP
sqloracle-databaseoracle11ggaps-and-islands
Dileep Kumar Kottakota
Dileep Kumar Kottakota

Reputation: 127

How to write Oracle query to group like below?

Id value 
1 5
1 6
1 8
1 9
1 10

Result should be like below

Id minValue maxValue
1   5               6
1   8               10

Previous value difference should be 1 otherwise need to insert other row

Upvotes: 0

Views: 74

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

This is famous GAPS and ISLANDS problem. You can read this wonderful article from Lalit Kumar B for detailed description. You can try below query - 

SELECT id, MIN(value), MAX(value)
FROM (SELECT id, value, value - ROW_NUMBER() OVER(PARTITION BY id ORDER BY value) rn
      FROM test) T
GROUP BY id, rn;

Here is the Fiddle

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191235

If you subtract the position of each value within the list of values for that ID (which you can get with an analytic function) from the value itself:

value - dense_rank() over (partition by id order by value)

then consecutive (or duplicate) values will get the same result:

select id, value,
  value - dense_rank() over (partition by id order by value)
from your_table;

        ID      VALUE        GRP
---------- ---------- ----------
         1          5          4
         1          6          4
         1          8          5
         1          9          5
         1         10          5

You can then aggregate using those differences:

select id, min(value) as minvalue, max(value) as maxvalue
from (
  select id, value,
    value - dense_rank() over (partition by id order by value) as grp
  from your_table
)
group by id, grp
order by id, minvalue;

        ID   MINVALUE   MAXVALUE
---------- ---------- ----------
         1          5          6
         1          8         10

db<>fiddle

You could use row_number() instead of dense_rank() if there are no duplicates.

Upvotes: 3

Related Questions