Oracle – Find the group of most consecutive occurrence of a value in a column

I have a table structure called test_table like this:

Timestamp Value
08:45:45 C 
08:45:59 E 
08:46:52 V 
08:46:59 C 
08:47:09 C 
08:48:00 C 
08:48:30 C 
08:48:55 C 
08:49:45 E 
08:50:45 E 
08:41:45 V 

and so on..

It is a large table and has about 3000 rows. I want to find the section of the table (start and end timestamp) for the longest running occurrence of “C” values. For example in the above case it would be 8:46:59 to 8:48:55. Is there an oracle query to extract that longest set of data from the large table?

Answer

There is a trick using the difference of row_number() values that you can use to identify sequences:

select value, count(*) as sequence_length, min(timestamp), max(timestamp)
from (select t.*,
             (row_number() over (order by timestamp) -
              row_number() over (partition by value order by timestamp)
             ) as seqnum
      from t
     ) t
where value = 'C'
group by seqnum, value
order by max(timestamp) - min(timestamp) desc;

To get just one row, you can use fetch first 1 row only in Oracle 12c+. In earlier versions, you can use:

select t.*
from (<subquery>
      order by . . .
     ) t
where rownum = 1;