MySQL maximum contiguous count query

Question

Does anyone know how to get the maximum sequence count from each column in a table. For example if have table A which has the following data

id | n1 | n2 | n3 | n4 | n5 | n6 | n7 | n8 | n9 | n10| Max | Sets
----------------------------------------------------------------
1  | 1  | 2  | 3  | 4  | 5  | 6  | 7  | 8  | 9  | 10 |  10 | 1 |
2  | 1  | 3  | 4  | 5  | 6  | 8  | 14 | 15 | 16 | 25 |  4  | 2 |
3  | 4  | 8  | 9  | 15 | 18 | 19 | 55 | 64 | 65 | 98 |  2  | 3 |
4  | 41 | 55 | 66 | 67 | 68 | 69 | 74 | 82 | 131| 132|  4  | 2 |

As you can see from the table above I need to get the maximum contiguous count of numbers from a row, in the above example I have added the column 'Max' & 'Sets' to show the required outcome.

In the first row there are 10 contiguous numbers (1 through 10) and only 1 set/group of continguous numbers, in the second there are only 4 (3,4,5,6) and 2 sets of continguous numbers (3 to 6 and 14 to 16).

I've been trying to figure this out for quite sometime but can't understand the query required (i.e. join or a straight select and if statements). I can do this in other languages but would rather prefer to have MySQL complete this calculation.

FOR i <= count(column)
IF count > sequence 
THEN sequence = count
ELSE count ++
NEXT i

SQL Fiddle Contains my table and data and I am trying to make required query

Good question.. tell me if it would be solved in some easier way — Sami, Nov 25 '12 at 15:16

JM Hicks · Answer 1 · 2012-11-27T08:37:18.410

Sorry, I didn't understand at first the comment about SQLFiddle. I tested the code there and found I needed to fix something more.

The query needed to have the n* columns moved from the final group-by expression to the aggregates, and a ( between the first FROM and 2nd SELECT. - And a COUNT() >= 2 instead of COUNT() > 2.

Now when run on your test code, it works.
Here is a URL for the test http://sqlfiddle.com/#!2/9e3cb/21/0

Corrected Answer:

SELECT id, MAX(n1) n1, MAX(n2) n2, MAX(n3) n3, MAX(n4) n4, MAX(n5) n5,  
MAX(n6) n6, MAX(n7) n7, MAX(n8) n8, MAX(n9) n9, MAX(n10) n10,  
MAX(GrpSize) Max, COUNT(GrpNumber) Sets  
FROM (  
    SELECT id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, GrpNumber, COUNT(*) GrpSize  
    FROM (  
        SELECT id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10,  
        CASE Numbers.N  
        WHEN 1 THEN n1 - N  
        WHEN 2 THEN n2 - N  
        WHEN 3 THEN n3 - N  
        WHEN 4 THEN n4 - N  
        WHEN 5 THEN n5 - N  
        WHEN 6 THEN n6 - N  
        WHEN 7 THEN n7 - N  
        WHEN 8 THEN n8 - N  
        WHEN 9 THEN n9 - N  
        WHEN 10 THEN n10 - N  
        END GrpNumber  
        FROM `mytbl`  
        CROSS JOIN (  
            SELECT 1 AS N UNION ALL  
            SELECT 2 AS N UNION ALL  
            SELECT 3 AS N UNION ALL  
            SELECT 4 AS N UNION ALL  
            SELECT 5 AS N UNION ALL  
            SELECT 6 AS N UNION ALL  
            SELECT 7 AS N UNION ALL  
            SELECT 8 AS N UNION ALL  
            SELECT 9 AS N UNION ALL  
            SELECT 10 AS N  
        ) Numbers  
    ) TT  
    GROUP BY id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10,GrpNumber  
    HAVING COUNT(*) >= 2  
) TT  
GROUP BY id

Original Answer:
Here's a way to do that with set-based query. This query supposes your table is named TableOfTen, and that it has exactly 10 n[umber][1..10] columns. It can be modified for any table (or derived table) for which the number of n[number][...] columns is known in advance.

SELECT id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, MAX(GrpSize) MaxSize, COUNT(GrpNumber) NumberOfSetsWithTwoOrMoreMembers  
FROM  (
    SELECT id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10,GrpNumber, COUNT(*) GrpSize  
    FROM 
        SELECT id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10,  
        CASE Numbers.N  
        WHEN 1 THEN n1 - N  
        WHEN 2 THEN n2 - N  
        WHEN 3 THEN n3 - N  
        WHEN 4 THEN n4 - N  
        WHEN 5 THEN n5 - N  
        WHEN 6 THEN n6 - N  
        WHEN 7 THEN n7 - N  
        WHEN 8 THEN n8 - N  
        WHEN 9 THEN n9 - N  
        WHEN 10 THEN n10 - N  
        END GrpNumber  
        FROM `mytbl`  
        CROSS JOIN (  
            SELECT 1 AS N UNION ALL  
            SELECT 2 AS N UNION ALL  
            SELECT 3 AS N UNION ALL  
            SELECT 4 AS N UNION ALL  
            SELECT 5 AS N UNION ALL  
            SELECT 6 AS N UNION ALL  
            SELECT 7 AS N UNION ALL  
            SELECT 8 AS N UNION ALL  
            SELECT 9 AS N UNION ALL  
            SELECT 10 AS N  
        ) Numbers  
    ) TT  
    GROUP BY id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10,GrpNumber  
    HAVING COUNT(*) > 2  
) TT  
GROUP BY id, n1, n2, n3, n4, n5, n6, n7, n8, n9, n10

score 0 · Answer 2 · answered Nov 25 '12 at 15:15

Complete code and Demo Link

declare c1,cnt,max,cmax,pv,cv,sets int;

cnt ---- for number of rows
c1 ----- loop invariant
max --- gets maximum contiguous values count in a rows of 10 columns
cmax -- current count of contiguous values
pv ----- previous column value of current row
cv ----- current column value of current row
sets --- tells how much sets of same max contiguous count in a row

After explaining variables I would feel only need to tell about the following repeating condition as well. if cv=pv+1 then set cmax=cmax+1; if cmax=max then set sets=sets+1; end if; if cmax>max then set max=cmax; end if; else set cmax=1;end if;set pv=cv;

When cv=pv+1 => current column value=previous column then we will increase cmax cmax=cmax+1; and we also need two checks as if cmax=max then sets=sets+1; and if cmax>max then max=cmax; and otherwise when cv!=pv+1 then cmax=1

I used a temporary table, inserted the Max_Count and No_of_Sets value for each row. At end it is shown

Hope it helps in what you want :)

MySQL maximum contiguous count query

2 Answers2