SQL - HSQL How to count consecutive occurences of a number

Question

I have this table:

code |  Seq  | incr | Bin
------------------------
AQ   |  2701 |  1   | 1  
AQ   |  2702 |  2   | 0  
AQ   |  2703 |  3   | 1  
AQ   |  2704 |  4   | 1  
AQ   |  2705 |  5   | 1  
AQ   |  2706 |  6   | 1  
AQ   |  2707 |  7   | 1  
AQ   |  2708 |  8   | 0  
AQ   |  2709 |  9   | 1  
AQ   |  2710 | 10   | 1  
AQ   |  2711 | 11   | 0  
AQ   |  2712 | 12   | 1  

I need to obtain the max count of consecutives '1' and the value of the first and last rows in "Seq" column of the resulting count. ie: for the written semplified table, the max count is '5' starting from "Seq" = 2703 ending in "Seq" = 2707

So I would like to have a resulting table which should be like this:

code |  SeqStart  | SeqEnd | Count
----------------------------------
AQ   |    2703    |  2707  |   5  
AR   |    2712    |  2722  |  11  

Trying to solve this I figured that

"incr" - (the "incr" value in the row with "Bin" = 0 and max "incr" but < "incr" which would be 2 for "incr" from 3 to 7 and 8 for "incr" from 9 to 10)

could be a good math way to do this but (ofc since I'm writing here) I can't find a way to write a working code for the proper second number of the subtraction for each row

Unfortunately since I'm using OpenOffice (3.4.1) I'm stuck on HSQLDB 1.8 If somebody could help me I'd really appreciate, thanks for reading

Answer 1

I found the solution by myself only using hsql 1.8 - I'm posting it if somebody else might be interested in it:

SELECT "a"."code", ("a"."Seq"-"b"."MaxNum" +1) AS "SeqStart", "a"."Seq" AS "SeqEnd", "b"."MaxNum" AS "Count" 
FROM 
    (SELECT DISTINCT "x"."code", "x"."Seq", "x"."incr", "x"."Bin", "y"."incr1", CASE WHEN "x"."Bin" = 1 THEN ("x"."incr"-"y"."incr1") ELSE '0' END AS "Num" 
    FROM "MyTable" AS "x" 
    LEFT JOIN 
    (SELECT "code", "incr", MAX("incr1") AS "incr1" 
    FROM 
        (SELECT "a"."code", "a"."incr", "b"."incr" AS "incr1" 
        FROM "MyTable" AS "a", "MyTable" AS "b" 
        WHERE ("b"."incr" BETWEEN '1' AND ("a"."incr" -1)) AND "b"."Bin" = 0) 
    GROUP BY "code", "incr") AS "y" 
    ON "y"."code" = "x"."code" AND "y"."incr" = "x"."incr") AS "a" 
INNER JOIN 
    (SELECT "code", MAX("Num") AS "MaxNum" 
    FROM 
        (SELECT DISTINCT "x"."code", "x"."Seq", "x"."incr", "x"."Bin", "y"."incr1", CASE WHEN "x"."Bin" = 1 THEN ("x"."incr"-"y"."incr1") ELSE '0' END AS "Num" 
        FROM "MyTable" AS "x" 
        LEFT JOIN 
        (SELECT "code", "incr", MAX("incr1") AS "incr1" 
        FROM 
            (SELECT "a"."code", "a"."incr", "b"."incr" AS "incr1" 
            FROM "MyTable" AS "a", "MyTable" AS "b" 
            WHERE ("b"."incr" BETWEEN '1' AND ("a"."incr" -1)) AND "b"."Bin" = 0) 
        GROUP BY "code", "incr") AS "y" 
        ON "y"."code" = "x"."code" AND "y"."incr" = "x"."incr") 
    GROUP BY "code") AS "b" 
ON "b"."code" = "a"."code" AND "b"."MaxNum" = "a"."Num"