Trouble with writing a query

Ok, here's a doozie that has me completely stumped. I've tried everything I can think of. Here is my data:


I need to group together all rows that which are the same process over consecutive checkpoints.


Desired result set:

Try this
select cp2.checkvalue, cp1.checkid, cp2.checkid
from checkpoint cp1
inner join checkpoint cp2
on cp1.checkvalue = cp2.checkvalue
where cp1.checkid<> cp2.checkid
group by cp1.checkvalue
...
You will have to adjust the inner joins for Oracle's non-ANSII style.

This seems to work.
Check out AskTom.oracle.com for more examples of analytics

create table x (check_point number, process varchar2(1));
insert into x values ( 1,'A');
insert into x values ( 2,'A');
insert into x values (10,'A');
insert into x values (11,'A');
insert into x values (16,'A');
insert into x values (17,'A');
insert into x values (18,'A');

insert into x values ( 3,'B');
insert into x values ( 4,'B');
insert into x values ( 5,'B');
insert into x values ( 6,'B');
insert into x values ( 7,'B');
insert into x values (15,'B');
insert into x values (16,'B');
insert into x values (17,'B');
insert into x values (18,'B');
insert into x values (19,'B');

insert into x values ( 6,'C');
insert into x values (16,'C');
insert into x values (21,'C');

select process, start_cp, check_point from
(select process,
case when lag_cp is null and lead_cp is null then check_point
else lag(check_point) over ( partition by process order by check_point)
end start_cp, check_point, lag_cp, lead_cp
from
(select process, check_point,
decode( lag(check_point) over (partition by process order by check_point),
check_point-1, lag(check_point) over ( partition by process order by check_point)) lag_cp,
decode( lead(check_point) over (partition by process order by check_point),
check_point+1, lead(check_point) over ( partition by process order by check_point)) lead_cp
from x)
where lag_cp is null or lead_cp is null)
where start_cp is not null
and (lag_cp is not null or start_cp = check_point)

Holy cow, that is slick. Just when I think I'm getting the hang of analytics, I see how much I've still got to learn.

Thanks!