How Can I Find the First Gap in a Running Counter Column Using SQL?

Use SQL to identify gaps in running counters

Finding gaps in run counter columns is a common task in data analysis. In SQL, there are several ways to achieve this, providing standard solutions that are compatible with various DBMSs.

Find the first gap

The goal is to identify the smallest missing positive integer from the counter column. For example, given [1, 2, 4, 5], the gap is 3.

MySQL, PostgreSQL

<code class="language-sql">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</code>

SQL Server

<code class="language-sql">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</code>

Oracle

<code class="language-sql">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</code>

ANSI (most common)

<code class="language-sql">SELECT  MIN(id) + 1
FROM    mytable mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    mytable mi 
        WHERE   mi.id = mo.id + 1
        )</code>

Sliding window function (if supported)

<code class="language-sql">SELECT  -- TOP 1
        -- 解除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
-- 解除PostgreSQL的以上注释</code>

The above is the detailed content of How Can I Find the First Gap in a Running Counter Column Using SQL?. For more information, please follow other related articles on the PHP Chinese website!