How to get the rows of the longest consecutive same value?

Table Learning:

Longest consecutive value is 1 (rowID 8-10). How to query to get the actual rows of consecutive values (not just rowStart and rowEnd values) like :

And for longest consecutive values of both 1 and 0?

DB Fiddle

CodePudding user response：

This is a gaps and islands problem, and one approach is to use the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY rowID) rn1,
              ROW_NUMBER() OVER (PARTITION BY values ORDER BY rowID) rn2
    FROM yourTable
),
cte2 AS (
    SELECT values, MIN(rowID) AS minRowID, MAX(rowID) AS maxRowID,
           RANK() OVER (PARTITION BY values ORDER BY MAX(rowID) - MIN(rowID) DESC) rnk
    FROM cte1
    GROUP BY values, rn1 - rn2
)

SELECT minRowID, maxRowID, values
FROM cte2
WHERE rnk = 1
ORDER BY values;

CodePudding user response：

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: