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

Question

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.

Answer 1

In MySQL and PostgreSQL, you can use like this:

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, you can try using like this:

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

In ANSI (works everywhere, least efficient), use this:

SELECT  MIN(id) + 1

FROM    mytable mo

WHERE   NOT EXISTS

        (

        SELECT  NULL

        FROM    mytable mi 

        WHERE   mi.id = mo.id + 1

        )

Systems supports sliding window functions this way:

SELECT  -- TOP 1

        -- Uncomment above for SQL Server 2012+

        previd

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