sqlgaps-and-islands

How do I find a "gap" in running counter with SQL?


I'd like to find the first "gap" in a counter column in an SQL table. For example, if there are values 1,2,4 and 5 I'd like to find out 3.

I can of course get the values in order and go through it manually, but I'd like to know if there would be a way to do it in SQL.

In addition, it should be quite standard SQL, working with different DBMSes.


Solution

  • In MySQL and PostgreSQL:

    SELECT  id + 1
    FROM    mytable mo
    WHERE   NOT EXISTS
            (
            SELECT  NULL
            FROM    mytable mi 
            WHERE   mi.id = mo.id + 1
            )
    ORDER BY
            id
    LIMIT 1
    

    In SQL Server:

    SELECT  TOP 1
            id + 1
    FROM    mytable mo
    WHERE   NOT EXISTS
            (
            SELECT  NULL
            FROM    mytable mi 
            WHERE   mi.id = mo.id + 1
            )
    ORDER BY
            id
    

    In Oracle:

    SELECT  *
    FROM    (
            SELECT  id + 1 AS gap
            FROM    mytable mo
            WHERE   NOT EXISTS
                    (
                    SELECT  NULL
                    FROM    mytable mi 
                    WHERE   mi.id = mo.id + 1
                    )
            ORDER BY
                    id
            )
    WHERE   rownum = 1
    

    ANSI (works everywhere, least efficient):

    SELECT  MIN(id) + 1
    FROM    mytable mo
    WHERE   NOT EXISTS
            (
            SELECT  NULL
            FROM    mytable mi 
            WHERE   mi.id = mo.id + 1
            )
    

    Systems supporting sliding window functions:

    SELECT  -- TOP 1
            -- Uncomment above for SQL Server 2012+
            previd + 1
    FROM    (
            SELECT  id,
                    LAG(id) OVER (ORDER BY id) previd
            FROM    mytable
            ) q
    WHERE   previd <> id - 1
    ORDER BY
            id
    -- LIMIT 1
    -- Uncomment above for PostgreSQL