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

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:

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

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

Edit: I meant how to get the actual rows of the same consecutive values not just the rowStart and the rowEnd values. Specifically the table below.

rowID	values
8	1
9	1
10	1

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:

grp_start	grp_end
8	10

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 *,
        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;