MySQL count the longest run length

Question

I am working with a MySQL / Maria database and am joining two MyISAM tables. I am returning a data set like below

ID Value1 Value2
1   xxx    xxx
2   xxx    xxx
3   xxx    xxx
8   xxx    xxx
9   xxx    xxx
10  xxx    xxx
11  xxx    xxx
18  xxx    xxx
19  xxx    xxx
20  xxx    xxx

I'm wondering if there is a way that I can return a count for the longest sequential series. To clarify 1,2,3 would be a run length of 3, 8-11 would be a run length of 4, 18-20 would be a run length of 3.

So what I want to return is just the value of "4"

I am running the query from a python script and can easily figure it out via python, but I am hoping there is a way I can do it via an sql query. I am potentially working with a few million records so I'm trying to avoid having to loop through all of those records.

It may help https://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql/29736658#29736658 — Jsowa, Oct 01 '20 at 14:48

score 0 · Answer 1 · answered Oct 01 '20 at 15:15

This is a gaps and islands problem, where you want the length of the biggest island.

You can use window functions:

select count(*) cnt, min(id) start_id, max(id) end_id
from (select id, row_number() over(order by id) rn from mytable t) t
group by id - rn
order by count(*) desc 
limit 1

MySQL count the longest run length

1 Answers1