0

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!

2
  • It's the remaining column that you're interested in? MySQL version CRITICAL!
    – Vérace
    May 6 '20 at 21:31
  • Either the remaining column or the occupied column. I am using MariaDB 10.2.31.
    – andyy15
    May 6 '20 at 21:40
1

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

2
  • Thank you Akina, this works perfectly!
    – andyy15
    May 6 '20 at 22:20
  • I checked on 10.2, 10.3, and 8.0 -- All worked; but I suspect it does not scale well, perhaps quadratic; slightly faster on MariaDB.
    – Rick James
    May 7 '20 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.