How to select longest consecutive positive streak rows in mysql?

Question

I have a table called rev. It has only two columns. Id and revNumber.

Here is my mysql fiddle http://sqlfiddle.com/#!9/86586e/1/0

id  revNumber
1     2
2     3
3     5
4    -3
5     1
6    -4 

Here consecutive positive numbers are 2,3 and 5 and 1 so the longest streak is 2,3,5 How can I select in mysql?

Thank you.

Answer 1

As you commented that you can upgrade to MySQL 8.0, here is a solution that uses window functions. You can compute a cumulative sum and compare it to the row number in order to identify the streaks. Sorting can be used to identify the longest streak, like:

The following query will give you a unique record that represents the longest streak of positive numbers (count of records, list of ids and list of revision numbers).

SELECT 
    COUNT(*) cnt, 
    GROUP_CONCAT(id ORDER BY id) ids,
    GROUP_CONCAT(rev_number ORDER BY id) rev_numbers
FROM (
    SELECT
        id,
        rev_number,
        ROW_NUMBER() OVER(ORDER BY id) rn,
        rev_number > 0 is_pos,
        SUM(CASE WHEN rev_number > 0 THEN 1 ELSE 0 END) OVER(ORDER BY id) sm
    FROM mytable
) x
GROUP BY (rn - sm), is_pos
ORDER BY cnt DESC
LIMIT 1

This demo on DB Fiddle with your sample data returns:

| cnt | ids   | rev_numbers |
| --- | ----- | ----------- |
| 3   | 1,2,3 | 2,3,5       |