Tabibitosan Method: Example 8


In the below example, the records with an id of 1-1 do not have any continuous records therefore the count of the continuous records would return nothing. The 2-2 record has two groups of continuous records, the first group has 4 continuous records and the
second group has 2 continuous records.

with ex11 as (
select '1-1' id,'SAND' lith,150 t_depth from dual union
select '1-1' id,'COAL' lith,200 t_depth from dual union
select '1-1' id,'SAND' lith,250 t_depth from dual union
select '1-1' id,'COAL' lith,300 t_depth from dual union
select '2-2' id,'SAND' lith, 75 t_depth from dual union
select '2-2' id,'COAL' lith,100 t_depth from dual union
select '2-2' id,'COAL' lith,150 t_depth from dual union
select '2-2' id,'COAL' lith,200 t_depth from dual union
select '2-2' id,'COAL' lith,250 t_depth from dual union
select '2-2' id,'SAND' lith,300 t_depth from dual union
select '2-2' id,'COAL' lith,400 t_depth from dual union
select '2-2' id,'COAL' lith,450 t_depth from dual)
select * from ex11;

Table created.

SQL> select * from ex11 order by lith,id;

ID  LITH    T_DEPTH
--- ---- ----------
1-1 COAL        200
1-1 COAL        300
1-1 SAND        150
1-1 SAND        250
2-2 COAL        100
2-2 COAL        150
2-2 COAL        200
2-2 COAL        250
2-2 COAL        400
2-2 COAL        450
2-2 SAND         75
2-2 SAND        300

12 rows selected.

SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) rn1,
  5   t_depth
  6  from ex11;

ID  LITH        RN1    T_DEPTH
--- ---- ---------- ----------
1-1 SAND          1        150
1-1 COAL          2        200
1-1 SAND          3        250
1-1 COAL          4        300
2-2 SAND          1         75
2-2 COAL          2        100
2-2 COAL          3        150
2-2 COAL          4        200
2-2 COAL          5        250
2-2 SAND          6        300
2-2 COAL          7        400
2-2 COAL          8        450

12 rows selected.

Expected output:

1-1 null
2-2 4
2-2 2


SQL> select
  2   id,
  3   lith,
  4   row_number() over (partition by id  order by id,t_depth) r1,
  5   row_number() over (partition by id,lith order by id,t_depth) r2,
  6   row_number() over (partition by id  order by id,t_depth)
  7    - row_number() over (partition by id,lith order by id,t_depth) r3,
  8   t_depth
  9  from ex11;

ID  LITH         R1         R2         R3    T_DEPTH
--- ---- ---------- ---------- ---------- ----------
1-1 SAND          1          1          0        150
1-1 COAL          2          1          1        200
1-1 SAND          3          2          1        250
1-1 COAL          4          2          2        300
2-2 SAND          1          1          0         75
2-2 COAL          2          1          1        100
2-2 COAL          3          2          1        150
2-2 COAL          4          3          1        200
2-2 COAL          5          4          1        250
2-2 SAND          6          2          4        300
2-2 COAL          7          5          2        400
2-2 COAL          8          6          2        450

12 rows selected.


SQL> select id,
  2         cnt
  3   from  
  4   (
  5   select  id,
  6     lith,
  7     count(*) cnt    
  8   from  (
  9     select
 10      id,
 11      lith,
 12      --row_number() over (partition by id  order by id,t_depth) r1,
 13      --row_number() over (partition by id,lith order by id,t_depth) r2,
 14      row_number() over (partition by id  order by id,t_depth)
 15       - row_number() over (partition by id,lith order by id,t_depth) r3
 16      --t_depth
 17     from ex11
 18     )
 19   group by id, lith,r3 );

ID         CNT
--- ----------
1-1          1
1-1          1
1-1          1
1-1          1
2-2          4
2-2          2
2-2          1
2-2          1

8 rows selected.

sort cnt in descending order to get something like below

ID         CNT
--- ----------
1-1          1    1
1-1          1    2
1-1          1    3
1-1          1    4
2-2          4    1
2-2          2    2
2-2          1    3
2-2          1    4

SQL> select id,
  2         cnt,
  3      row_number() over ( partition by id order by cnt desc) rn
  4   from  
  5   (
  6   select  id,
  7     lith,
  8     count(*) cnt    
  9   from  (
 10     select
 11      id,
 12      lith,
 13      --row_number() over (partition by id  order by id,t_depth) r1,
 14      --row_number() over (partition by id,lith order by id,t_depth) r2,
 15      row_number() over (partition by id  order by id,t_depth)
 16       - row_number() over (partition by id,lith order by id,t_depth) r3
 17      --t_depth
 18     from ex11
 19     )
 20   group by id, lith,r3 );

ID         CNT         RN
--- ---------- ----------
1-1          1          1
1-1          1          2
1-1          1          3
1-1          1          4
2-2          4          1
2-2          2          2
2-2          1          3
2-2          1          4

8 rows selected.

consider rn=1 for both id and cnt >1

SQL>   select id, cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1          1
2-2          4
2-2          2


SQL>   select id, nullif(cnt,1) cnt
  2    from (
  3      select id,
  4      cnt,
  5      row_number() over ( partition by id order by cnt desc) rn
  6      from  
  7      (
  8      select  id,
  9     lith,
 10     count(*) cnt    
 11      from  (
 12     select
 13      id,
 14      lith,
 15      --row_number() over (partition by id  order by id,t_depth) r1,
 16      --row_number() over (partition by id,lith order by id,t_depth) r2,
 17      row_number() over (partition by id  order by id,t_depth)
 18       - row_number() over (partition by id,lith order by id,t_depth) r3
 19      --t_depth
 20     from ex11
 21     )
 22      group by id, lith,r3 ))
 23    where rn=1 or cnt>1;

ID         CNT
--- ----------
1-1
2-2          4
2-2          2

Tabibitosan Method: Example 7

Get consecutive date range (without any gap) per  group

SQL> create table ex8
  2  ( id number
  3  , dt date);

Table created.

SQL> insert into ex8 values (2, to_date( '01-01-2014','dd-mm-yyyy' ));

1 row created.

SQL> insert into ex8 values (2, to_date( '02-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '03-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '04-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '05-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '06-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '07-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '08-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '09-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '10-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '11-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (3, to_date( '12-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (4, to_date( '13-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (5, to_date( '14-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '15-01-2014','dd-mm-yyyy'));

1 row created.

SQL> insert into ex8 values (2, to_date( '16-01-2014','dd-mm-yyyy'));

1 row created.

SQL> commit;

Commit complete.

Expected result:
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014




SQL> select id, min(dt), max(dt) from
  2  (
  3  select
  4   id,
  5   dt,
  6   dt - row_number() over (partition by id order by dt,id) grp
  7  from ex8)
  8  group by id,grp
  9  order by min(dt);

        ID MIN(DT)    MAX(DT)
---------- ---------- ----------
         2 01-01-2014 03-01-2014
         3 04-01-2014 08-01-2014
         2 09-01-2014 10-01-2014
         3 11-01-2014 12-01-2014
         4 13-01-2014 13-01-2014
         5 14-01-2014 14-01-2014
         2 15-01-2014 16-01-2014

7 rows selected.

Tabibitosan Method: Example 6
Get count of latest consecutive years per group

SQL>  create table ex8 as(
  2   select 1 as ID,2009 as yr from dual union
  3   select 1,2008 from dual union
  4   select 1,2007 from dual union
  5   select 1,2006 from dual union
  6   select 1,2005 from dual union
  7   select 1,2004 from dual union
  8   select 1,2002 from dual union
  9   select 1,2001 from dual union
 10   select 2,2009 from dual union
 11   select 2,2008 from dual union
 12   select 2,2005 from dual);

Table created.

SQL> select * from ex8;

        ID         YR
---------- ----------
         1       2001
         1       2002
         1       2004
         1       2005
         1       2006
         1       2007
         1       2008
         1       2009
         2       2005
         2       2008
         2       2009

11 rows selected.

Expected output:

1    6
2    2

SQL> select
  2   yr - row_number() over ( partition by id order by yr)
  3  from ex8;

YR-ROW_NUMBER()OVER(PARTITIONBYIDORDERBYYR)
-------------------------------------------
                                       2000
                                       2000
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2001
                                       2004
                                       2006
                                       2006

11 rows selected.

SQL> select  id
  2    yr,
  3    yr - row_number() over ( partition by id order by yr) seq
  4  from ex8;

        YR        SEQ
---------- ----------
         1       2000
         1       2000
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         1       2001
         2       2004
         2       2006
         2       2006

11 rows selected.

SQL> select id,count(*) keep (dense_rank last order by grp) cnt
  2  from (
  3    select  id,
  4      yr,
  5      yr - row_number() over ( partition by id order by yr) grp
  6    from ex8)
  7  group by id;

        ID        CNT
---------- ----------
         1          6
         2          2

Tabibitosan Method: Example 5

Sequencial value range (without any gap) for same composite column groups

SQL> create table ex7 (sortKey,Val1,Val2) as
  2  select 1,'A','X' from dual union all
  3  select 2,'A','X' from dual union all
  4  select 3,'B','Y' from dual union all
  5  select 4,'B','Y' from dual union all
  6  select 5,'A','X' from dual union all
  7  select 5,'B','X' from dual union all
  8  select 6,'A','Y' from dual union all
  9  select 7,'B','Y' from dual union all
 10  select 7,'A','Y' from dual union all
 11  select 8,'A','Y' from dual;

Table created.

SQL> select * from ex7;

   SORTKEY V V
---------- - -
         1 A X
         2 A X
         ----------
         3 B Y
         4 B Y
         ------------
         5 A X
         -----------
         5 B X
         -----------------
         6 A Y
         -----------
         7 B Y
         --------------
         7 A Y
         8 A Y

10 rows selected.

Expected Result:

MIN(SORTKEY) MAX(SORTKEY) V V
------------ ------------ - -
           1            2 A X
           3            4 B Y
           5            5 A X
           5            5 B X
           6            8 A Y
           7            7 B Y


/* partition on val1, val2 and then sort on sortkey */

SQL> select sortkey,
  2         val1, val2 ,
  3         row_number() over ( partition by val1,val2 order by sortkey,val1,val2 ) diff,
  4      sortkey - row_number() over ( partition by val1,val2 order by sortkey,val1,val2 ) grp
  5  from ex7;

   SORTKEY V V       DIFF        GRP
---------- - - ---------- ----------
         1 A X          1          0
         2 A X          2          0
         5 A X          3          2
         6 A Y          1          5
         7 A Y          2          5
         8 A Y          3          5
         5 B X          1          4
         3 B Y          1          2
         4 B Y          2          2
         7 B Y          3          4

10 rows selected.

SQL> select min(sortkey),max(sortkey),val1,val2
  2      from (
  3      select sortkey,
  4            val1, val2 ,
  5             row_number() over ( partition by val1,val2 order by sortkey ) diff,
  6          sortkey - row_number() over ( partition by val1,val2 order by sortkey ) grp
  7     from ex7 )
  8    group by val1,val2,grp
  9  order by min(sortkey);

MIN(SORTKEY) MAX(SORTKEY) V V
------------ ------------ - -
           1            2 A X
           3            4 B Y
           5            5 A X
           5            5 B X
           6            8 A Y
           7            7 B Y

6 rows selected.

Aketi,in OTN has posted the solution as below:

SQL> select min(sortKey) as sta,max(sortKey),Val1,Val2 as end
  2      from (select sortKey,Val1,Val2,
  3             dense_rank() over(order by sortKey)
  4            -Row_Number() over(partition by Val1,Val2
  5                               order by sortKey)
  6            as distance
  7             from ex7)
  8      group by Val1,Val2,distance
  9      order by min(sortKey);

       STA MAX(SORTKEY) V E
---------- ------------ - -
         1            2 A X
         3            4 B Y
         5            5 A X
         5            5 B X
         6            8 A Y
         7            7 B Y

6 rows selected.

I think the addition dense_rank function is not required as already we have a sortkey column.

Tabibitosan Method: Example 4

Collecting date range based on group:

create table ex5 (date_worked,country)
as
select to_date('1-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('4-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('5-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('6-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('7-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('8-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('11-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('12-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('13-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('14-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('15-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('18-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('19-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('20-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('21-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('22-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('25-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual union all
select to_date('26-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('27-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('28-Nov-13','dd-Mon-yy'), 'India' from dual union all
select to_date('29-Nov-13','dd-Mon-yy'), 'United Kingdom' from dual
/
SQL> select * from ex5;

DATE_WORK COUNTRY
--------- --------------
01-NOV-13 United Kingdom
04-NOV-13 United Kingdom
05-NOV-13 India
06-NOV-13 India
07-NOV-13 India
08-NOV-13 United Kingdom
11-NOV-13 United Kingdom
12-NOV-13 India
13-NOV-13 India
14-NOV-13 India
15-NOV-13 United Kingdom
18-NOV-13 United Kingdom
19-NOV-13 India
20-NOV-13 India
21-NOV-13 India
22-NOV-13 United Kingdom
25-NOV-13 United Kingdom
26-NOV-13 India
27-NOV-13 India
28-NOV-13 India
29-NOV-13 United Kingdom

21 rows selected.

Expected result:
    COUNTRY        START_DATE END_DATE
    -------------- ---------- ----------
    United Kingdom 01-11-2013 04-11-2013
    India          05-11-2013 07-11-2013
    United Kingdom 08-11-2013 11-11-2013
    India          12-11-2013 14-11-2013
    United Kingdom 15-11-2013 18-11-2013
    India          19-11-2013 21-11-2013
    United Kingdom 22-11-2013 25-11-2013
    India          26-11-2013 28-11-2013
    United Kingdom 29-11-2013 29-11-2013

SQL> select date_worked,
  2         country,
  3          row_number() over ( order by date_worked) rn,
  4         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  5  from ex5;

DATE_WORK COUNTRY                RN        GRP
--------- -------------- ---------- ----------
01-NOV-13 United Kingdom          1          0
04-NOV-13 United Kingdom          2          0
05-NOV-13 India                   3          2
06-NOV-13 India                   4          2
07-NOV-13 India                   5          2
08-NOV-13 United Kingdom          6          3
11-NOV-13 United Kingdom          7          3
12-NOV-13 India                   8          4
13-NOV-13 India                   9          4
14-NOV-13 India                  10          4
15-NOV-13 United Kingdom         11          6
18-NOV-13 United Kingdom         12          6
19-NOV-13 India                  13          6
20-NOV-13 India                  14          6
21-NOV-13 India                  15          6
22-NOV-13 United Kingdom         16          9
25-NOV-13 United Kingdom         17          9
26-NOV-13 India                  18          8
27-NOV-13 India                  19          8
28-NOV-13 India                  20          8
29-NOV-13 United Kingdom         21         12

21 rows selected.

SQL> select min(date_worked),min(date_worked)
  2  from
  3  (
  4  select date_worked,
  5         country,
  6      row_number() over ( order by date_worked) rn,
  7         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  8  from ex5
  9  )
 10  group by grp
 11  order by min(rn);

SQL> select country,min(date_worked),max(date_worked)
  2  from
  3  (
  4  select date_worked,
  5         country,
  6      row_number() over ( order by date_worked) rn,
  7         row_number() over ( order by date_worked)-row_number() over (partition by country order
by date_worked) grp
  8  from ex5
  9  )
 10  group by country,grp
 11  order by min(rn);

COUNTRY        MIN(DATE_ MAX(DATE_
-------------- --------- ---------
United Kingdom 01-NOV-13 04-NOV-13
India          05-NOV-13 07-NOV-13
United Kingdom 08-NOV-13 11-NOV-13
India          12-NOV-13 14-NOV-13
United Kingdom 15-NOV-13 18-NOV-13
India          19-NOV-13 21-NOV-13
United Kingdom 22-NOV-13 25-NOV-13
India          26-NOV-13 28-NOV-13
United Kingdom 29-NOV-13 29-NOV-13

9 rows selected.

SQL>  

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

Tabibitosan Method: Example 2

Grouping consecutive month range without gaps

SQL> create table ex100
  2  as
  3  select date '2013-01-01' dt from dual union all
  4  select date '2013-02-01' from dual union all
  5  select date '2013-03-01' from dual union all
  6  select date '2013-06-01' from dual union all
  7  select date '2013-07-01' from dual union all
  8  select date '2013-10-01' from dual union all
  9  select date '2013-12-01' from dual union all
 10  select date '2014-01-01' from dual
 11  /

Table created.

SQL> select * from ex100;

DT
---------
01-JAN-13
01-FEB-13
01-MAR-13
01-JUN-13
01-JUL-13
01-OCT-13
01-DEC-13
01-JAN-14

8 rows selected.

Expected result:


01-JAN-13 01-MAR-13
01-JUN-13 01-JUL-13
01-OCT-13 01-OCT-13
01-DEC-13 01-JAN-14


ie, group by consective month range without gaps.

SQL> select dt,
  2         extract(year  from dt)*12
  3         +extract(month from dt),
  4      row_number()over (order by dt) rn,
  5      extract(year  from dt)*12
  6         + extract(month from dt)
  7      - row_number()over (order by dt) grp
  8  from ex100;

DT        EXTRACT(YEARFROMDT)*12+EXTRACT(MONTHFROMDT)         RN        GRP
--------- ------------------------------------------- ---------- ----------
01-JAN-13                                       24157          1      24156
01-FEB-13                                       24158          2      24156
01-MAR-13                                       24159          3      24156
01-JUN-13                                       24162          4      24158
01-JUL-13                                       24163          5      24158
01-OCT-13                                       24166          6      24160
01-DEC-13                                       24168          7      24161
01-JAN-14                                       24169          8      24161

8 rows selected.

SQL> select min(dt), max(dt)
  2  from (
  3  select dt,
  4         extract(year  from dt)*12
  5         +extract(month from dt),
  6      row_number()over (order by dt) rn,
  7      extract(year  from dt)*12
  8         + extract(month from dt)
  9      - row_number()over (order by dt) grp
 10  from ex100)
 11  group by grp
 12  order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14

or,get the count of months from an initial month say '01-01-1900'

SQL> select dt,
  2           months_between(dt, to_date('01/01/1900','dd/mm/yyyy')),
  3           row_number()over (order by dt) rn
  4    from ex100;

DT        MONTHS_BETWEEN(DT,TO_DATE('01/01/1900','DD/MM/YYYY'))             RN
--------- -----------------------------------------------------     ----------
01-JAN-13                                                  1356              1
01-FEB-13                                                  1357              2
01-MAR-13                                                  1358                3
01-JUN-13                                                  1361              4
01-JUL-13                                                  1362               5
01-OCT-13                                                  1365              6
01-DEC-13                                                  1367              7
01-JAN-14                                                  1368               8

8 rows selected.


SQL> select min(dt), max(dt)
  2    from (
  3    select dt,
  4           months_between(dt, to_date('01/01/1900','dd/mm/yyyy'))
  5           -row_number()over (order by dt) grp
  6    from ex100)
  7    group by grp
  8    order by min(dt);

MIN(DT)       MAX(DT)
---------     ---------
01-JAN-13     01-MAR-13
01-JUN-13     01-JUL-13
01-OCT-13     01-OCT-13
01-DEC-13     01-JAN-14