Mysql – How to find the first row of the longest occurrences of repeating numbers

gaps-and-islandsMySQL

I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,0
2,East,1_1_2,1,0
3,East,1_1_3,1,4
4,East,1_1_4,1,0
5,East,1_1_5,1,0
6,East,1_2_1,1,3
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4 
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,4

I would like to find the beginning of the longest string of repeating numbers from that position to the end of the table.

For the example above, the mysql statement should return row id of 8 because from row 8 to the end of the table the value in the occupied and remaining columns are 0 and 4.

Any help on writing this as a mysql select statement would be greatly appreciated!

Best Answer

For MySQL version 8+

WITH 
cte1 AS
(
SELECT id, 
       CASE WHEN     occupied  = LAG(occupied) OVER (ORDER BY id)
                 AND remaining = LAG(remaining) OVER (ORDER BY id)
            THEN 0
            ELSE 1 
            END values_differs
FROM test
),
cte2 AS 
(
SELECT id,
       SUM(values_differs) OVER (ORDER BY id) group_num
FROM cte1
ORDER BY id
)
SELECT MIN(id)
FROM cte2
GROUP BY group_num
ORDER BY COUNT(*) DESC LIMIT 1;

fiddle

Related Question