Is there a way to get max consecutive counts of 1s across columns in SQL?

Question

Is there a way to get maximum consecutive counts across columns in SQL? I'm looking for longest duration of survival.

For example, if I have a dataset that looks like this

ID  T1  T2  T3  T4  T5  T6  T7  T8  T9
1   1   1   0   0   0   1   1   1   1
2   0   0   0   1   1   1   1   1   0
3   0   1   0   1   0   1   1   0   0
4   0   1   0   0   0   0   0   0   0
5   0   1   1   0   0   0   0   0   0
6   1   0   1   1   0   1   1   1   0

I want to add a column to get the maximum consecutive 1s across the columns T1-T9 so it would look like this

ID  T1  T2  T3  T4  T5  T6  T7  T8  T9  MAX
1   1   1   0   0   0   1   1   1   1   4
2   0   0   0   1   1   1   1   1   0   5
3   0   1   0   1   0   1   1   0   0   2
4   0   1   0   0   0   0   0   0   0   1
5   0   1   1   0   0   0   0   0   0   2
6   1   0   1   1   0   1   1   1   0   3

Answer 1

**The below code is a way to get maximum consecutive counts across Column in MySQL I think you want across the Row **

create table t(id integer,t1 integer,t2 integer,t3 integer,t4 integer,t5 integer,t6 integer,t7 integer,t8 integer,t9 integer);
insert into t values(1,1,0,1,0,1,1,0,0,0),(2,0,0,1,1,1,0,0,0,0),(3,1,0,1,1,1,1,0,0,0);

WITH CTE1 AS
( 
       SELECT id , ROW_NUMBER() OVER (ORDER BY id) Rnk FROM t 
)
,CTE2 AS
(
       SELECT *,  CASE WHEN id-1 = LAG(id) OVER(ORDER BY rnk) THEN 0
       ELSE 1 END cols FROM CTE1 c2
)
,CTE3 AS
(
       SELECT *,  SUM(cols) OVER(ORDER BY rnk) Grouper FROM CTE2 c2
)
SELECT *  FROM
(
    SELECT COUNT(*) Counts FROM CTE3 GROUP BY Grouper  
)r
ORDER BY Counts DESC ;