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.