TabibitoSan method: Example 3
Get Rows continued and grouping on ID (ID Equal)

create table Ex4 (ID,Val,SortKey) as
select 1, 5, 1 from dual union all
select 1,10, 2 from dual union all
select 2, 2, 3 from dual union all
select 2, 5, 4 from dual union all
select 1,15, 5 from dual union all
select 3,25, 6 from dual union all
select 3,10, 7 from dual union all
select 3, 5, 8 from dual union all
select 3,15, 9 from dual union all
select 4, 5,10 from dual;

SQL> select * from ex4;

        ID        VAL    SORTKEY
---------- ---------- ----------
         1          5          1
         1         10          2
         2          2          3
         2          5          4
         1         15          5
         3         25          6
         3         10          7
         3          5          8
         3         15          9
         4          5         10

10 rows selected.

Expected Result:

         1          5         10
         2          2          5
         1         15         15
         3          5         25
         4          5          5


SQL> select id, min(val), max(val) from
  2  (select id, val,  sortkey,
  3           sortkey - row_number() over ( partition by id order by sortkey) grp
  4  from ex4)
  5  group by id, grp
  6  order by min(sortkey);

        ID   MIN(VAL)   MAX(VAL)
---------- ---------- ----------
         1          5         10
         2          2          5
         1         15         15
         3          5         25
         4          5          5

Here is a similar post from www.Orafaq,com:

Problem definition:
I'm trying to work out how to take a table like this:

ID Date
125 02-Feb-07
125 16-Mar-07
125 23-May-07
125 24-May-07
125 25-May-07
333 02-Jan-09
333 03-Jan-09
333 04-Jan-09
333 17-Mar-09

And display the data like this:

ID Period Period Start Period End
125 1 02-Feb-07 02-Feb-07
125 2 16-Mar-07 16-Mar-07
125 3 23-May-07 25-May-07
333 1 02-Jan-09 04-Jan-09
333 2 17-Mar-09 17-Mar-09

As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.

Solution:

with mytable(ID,dayC) as (
select 125,date '2007-02-02' from dual union
select 125,date '2007-05-16' from dual union
select 125,date '2007-05-23' from dual union
select 125,date '2007-05-24' from dual union
select 125,date '2007-05-25' from dual union
select 333,date '2009-01-02' from dual union
select 333,date '2009-01-03' from dual union
select 333,date '2009-01-04' from dual union
select 333,date '2009-03-17' from dual)
select ID,Row_Number() over(partition by ID order by dis) as rn,
min(dayC) as staD,max(dayC) as endD
from (select ID,dayC,
      dayC-Row_Number() over(partition by ID order by dayC) as dis
      from mytable)
group by ID,dis
order by ID,dis;

 ID  RN  STAD      ENDD
---  --  --------  --------
125   1  07-02-02  07-02-02
125   2  07-05-16  07-05-16
125   3  07-05-23  07-05-25
333   1  09-01-02  09-01-04
333   2  09-03-17  09-03-17