Select consecutive rows under a certain value in MySQL

Selecting consecutive rows with a column under a certain value

I have a table with the following data:

crashID     crash
-----------------------
1           189
2           144
3           8939        
4           748
5           988
6           102
7           392
8           482
9           185
10          101

I want to select the longest streak of consecutive rows that also have a crash value below a certain treshold. Let’s say 500 for this example.

How do I go about doing this in a single MySQL query? (v8.0.1)

Desired output would be this:

crashID     crash
------------------
6           102
7           392
8           482
9           185
10          101

Answer

You can try to solve it using gaps and islands approach, assume every crash lte 500 is an island then find the largest island:

SET @threshold = 500;
WITH cte1 AS (
    SELECT
        crashID,
        CASE WHEN crash <= @threshold THEN 1 ELSE 0 END AS island,
        ROW_NUMBER() OVER (ORDER BY crashID) rn1,
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN crash <= @threshold THEN 1 ELSE 0 END ORDER BY crashID) rn2
    FROM t
), cte2 AS (
    SELECT MIN(crashID) AS fid, MAX(crashID) AS tid
    FROM cte1
    WHERE island = 1
    GROUP BY rn1 - rn2
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
SELECT *
FROM t
WHERE crashID BETWEEN (SELECT fid FROM cte2) AND (SELECT tid FROM cte2);

DB Fiddle