Oracle: enumerate groups of similar rows

Question

Hi all!

I have the following table:

ID  |  X  
1   |  1  
2   |  2  
3   |  5  
4   |  6
5   |  7
6   |  9  

I need to enumerate groups of rows in such way that if row i and i-1 differ in column X by less than 2 they should have the same group number N. See example below.

ID  |  X  | N
1   |  1  | 1
2   |  2  | 1
3   |  5  | 2
4   |  6  | 2
5   |  7  | 2
6   |  9  | 3

Note that rows X(2)-X(1)=1 so they are grouped in the first group. Than X(3)-X(2)=3 so the 3rd row goes to 2nd group with 3rd and 4th row. X(6)-X(5)=2 so 6th row is in the 3rd group.

Can anybody help me with writing SQL query that will return the second table?

Answer 1

This should do it:

select id, x, sum(new_group) over (order by id) as group_no
from
( select id, x, case when x-prev_x = 1 then 0 else 1 end new_group
  from
  ( select id, x, lag(x) over (order by id) prev_x
    from mytable
  )
);

I get the correct answer for your data with that query.

Answer 2

SQL> create table mytable (id,x)
  2  as
  3  select 1, 1 from dual union all
  4  select 2, 2 from dual union all
  5  select 3, 5 from dual union all
  6  select 4, 6 from dual union all
  7  select 5, 7 from dual union all
  8  select 6, 9 from dual
  9  /

Table created.

SQL> select id
  2       , x
  3       , sum(y) over (order by id) n
  4    from ( select id
  5                , x
  6                , case x - lag(x) over (order by id)
  7                  when 1 then 0
  8                  else 1
  9                  end y
 10             from mytable
 11         )
 12   order by id
 13  /

        ID          X          N
---------- ---------- ----------
         1          1          1
         2          2          1
         3          5          2
         4          6          2
         5          7          2
         6          9          3

6 rows selected.

Which is essentially the same as Tony's answer, only one inline view less.

Regards, Rob.

Answer 3

Using basic operations only:

create table test(id int, x int);
insert into test values(1, 1), (2, 2), (3, 5), (4, 6), (5, 7), (6, 9);

create table temp as 
select rownum() r, 0 min, x max 
from test t 
where not exists(select * from test t2 where t2.x = t.x + 1);

update temp t set min = select max + 1 from temp t2 where t2.r = t.r - 1;
update temp t set min = 0 where min is null;

select * from temp order by r;

select t.id, t.x, x.r from test t, temp x where t.x between x.min and x.max;

drop table test;
drop table temp;