If you are using Oracle 12c (or later), have a look at MATCH_RECOGNIZE()
Test table / data
-- id 5: not in the image you've uploaded
-- id 6: dstop value taken from the uploaded image
create table wt( id, dstart, dstop )
as
select 1 as id, 1.5 as DSTART, 3 as DSTOP from dual union all
select 2 as id, 2 as DSTART, 3.5 as DSTOP from dual union all
select 3 as id, 4.5 as DSTART, 8 as DSTOP from dual union all
select 4 as id, 7 as DSTART, 9 as DSTOP from dual union all
select 7 as id, 7 as DSTART, 12 as DSTOP from dual union all
select 6 as id, 7 as DSTART, 11.5 as DSTOP from dual
;
Query
select *
from wt
match_recognize (
order by id
measures
first( dstart ) firststart_
, last( dstop) laststop_
one row per match
pattern( A B* )
define
B as prev( dstop ) > dstart
) ;
-- result
+-----------+---------+
|FIRSTSTART_|LASTSTOP_|
+-----------+---------+
|1.5 |3.5 |
|4.5 |12 |
+-----------+---------+
See DBfiddle.
EDIT: for Oracle 11g
As you are using Oracle 11g (information added after the initial answer), you cannot use MATCH_RECOGNIZE(). However, you can probably use analytic functions for this. One of the problems is, that we need to {1} find the "groups" that the IDs and values belong to. Once this is achieved, we can {2} use GROUP BY, and call MIN() and MAX() to get the results.
{1} find groups
select id, value_, startstop_
, case
when startstop_ = 1 and lag_ < value_ then 1 -- group start
else 0 -- in group
end groupstart_
from (
select id, value_, startstop_
, lag( value_, 1, 0 ) over ( order by id, value_ ) lag_
from wt
unpivot (
value_ for startstop_ in ( dstart as 1, dstop as -1 )
)
)
;
+--+------+----------+-----------+
|ID|VALUE_|STARTSTOP_|GROUPSTART_|
+--+------+----------+-----------+
|1 |1.5 |1 |1 |
|1 |3 |-1 |0 |
|2 |2 |1 |0 |
|2 |3.5 |-1 |0 |
|3 |4.5 |1 |1 |
|3 |8 |-1 |0 |
|4 |7 |1 |0 |
|4 |9 |-1 |0 |
|6 |7 |1 |0 |
|6 |11.5 |-1 |0 |
|7 |7 |1 |0 |
|7 |12 |-1 |0 |
+--+------+----------+-----------+
{2} Numbering of groups
create or replace view group_view as
with cte_ as ( -- CTE - Oracle: "subquery factoring"
select id, value_, startstop_
, case
when startstop_ = 1 and lag_ < value_ then 1 -- group start
else 0 -- in group
end groupstart_
from (
select id, value_, startstop_
, lag( value_, 1, 0 ) over ( order by id, value_ ) lag_
from wt
unpivot (
value_ for startstop_ in ( dstart as 1, dstop as -1 )
)
)
)
select
id, value_, startstop_
, sum( groupstart_ ) over ( order by id ) group_
from cte_ ;
You don't necessarily need to code a view (this has just been added for clarity here). Also, you may be able to refactor the above code, and make it more compact.
Now we can:
select * from group_view ;
+--+------+----------+------+
|ID|VALUE_|STARTSTOP_|GROUP_|
+--+------+----------+------+
|1 |1.5 |1 |1 |
|1 |3 |-1 |1 |
|2 |2 |1 |1 |
|2 |3.5 |-1 |1 |
|3 |4.5 |1 |2 |
|3 |8 |-1 |2 |
|4 |7 |1 |2 |
|4 |9 |-1 |2 |
|6 |7 |1 |2 |
|6 |11.5 |-1 |2 |
|7 |7 |1 |2 |
|7 |12 |-1 |2 |
+--+------+----------+------+
-- and
select group_, min( value_ ), max( value_ )
from group_view
group by group_
order by 1
;
+--+---------------+---------------+
|G_|MIN(STARTSTOP_)|MAX(STARTSTOP_)|
+--+---------------+---------------+
|1 |1.5 |3.5 |
|2 |4.5 |12 |
+--+---------------+---------------+
SQLfiddle here.