How to find a gap in range in SQL

gaps-and-islands, postgresql, sql, sqlite

Solution

in mysql and postgresql

SELECT  id + 1
FROM    test mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    test mi 
        WHERE   mi.id = mo.id + 1
        ) and mo.id> 100
ORDER BY
        id
LIMIT 1

fiddle for mysql and fiddle for postgresql

in ms sql

SELECT  TOP 1
        id + 1
FROM    test mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    test mi 
        WHERE   mi.id = mo.id + 1
        )
          and mo.id > 100
ORDER BY
        id

fiddle

Problem

This question explains how to find the first "unused" number in a table, but how can I find the same so that I can define extra constraints. How do I alter the query so that I get the first unused number after that's greater than 100 e.g. If I have 23, 56, 100, 101, 103 in my table i should get 102.

Original source

Related problems