How to get longest consecutive same value?

Question

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

Table Learning:

rowID	values
1	1
2	1
3	0
4	0
5	0
6	1
7	0
8	1
9	1
10	1

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

rowID	values
8	1
9	1
10	1

And for longest consecutive values of both 1 and 0?

DB Fiddle

Answer 1

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 *,
        MIN(rowID) OVER (PARTITION BY values, rn1 - rn2) AS minRowID,
        MAX(rowID) OVER (PARTITION BY values, rn1 - rn2) AS maxRowID
    FROM cte1
),
cte3 AS (
    SELECT *, RANK() OVER (PARTITION BY values ORDER BY maxRowID - minRowID DESC) rnk
    FROM cte2
)

SELECT rowID, values
FROM cte3
WHERE rnk = 1
ORDER BY values, rowID;