Get the Rows of the Longest Consecutive Same Value of the Whole Table

I hope you are doing well! Sorry for the basic question, however, I have been struggling to get the rows of the longest consecutive same value of the whole table and I was wondering what the query would be to achieve this?

Example, let’s say that I have the table Learning:

The longest consecutive rows in the table Learning where values = 1 is rowID 8-10 as rowID 1-2 is 2 and rowID 6-6 is 1. How could I achieve this query? And for learning purposes, if I were to want the values of both 1 and 0, is there a query to get both rows of the longest consecutive values? Or would I just have to run the query two times, switching the values per query.

I have created a DB Fiddle for anyone who wants to test their query: https://www.db-fiddle.com/f/kzTjXmHMdndrnytP7SstK2/0

Thanks for your time and please let me know!

Thanks,

Marcos

>Solution :

I think that the simplest approach is to use a window count to define the islands. Then to get the "longest" island, we just need to aggregate, sort and limit:

select min(valueid) grp_start, max(valueid) grp_end 
from (select t.*, sum(value = 0) over(order by valueid) grp from testing t) t
where value = 1
group by grp
order by count(*) desc limit 1

In the DB Fiddle that you provided, the query returns: