Find missing values within range of running counter

Question

I want to find missing values within range of a counter with gaps and then make sure the value displayed is within 1000 to 65000.

To get over 1000, I can modify How do I find a "gap" in running counter with SQL? but how do I make sure i pick a value under 65000? For reference the underlying table has a few 100 rows and will never have more than 10k rows, but the existing values are already in from 1 through 65000 (Randomly increasing numbers).

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

score 1 · Accepted Answer · answered Jul 01 '20 at 15:28

1

SELECT a.id+1000 AS start, MIN(b.id) - 1 AS end
    FROM mytable AS a, mytable AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

or

SELECT  id + 1000
FROM    users mo
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    users mi 
        WHERE   mi.id = mo.id + 1
        ) AND id < 65000
ORDER BY
        id

try this, it will print all the gaps

answered Jul 01 '20 at 15:28

Niteesh

2,742
3
12
33

thanks, it works but i have 95 rows less than 64000 and for some reason this query is only returning half. – Illusionist Jul 01 '20 at 15:42
what is the first id in the table ? – Niteesh Jul 01 '20 at 15:43
depending on that i'll give you a solution – Niteesh Jul 01 '20 at 15:44
first id is 1 but i want new ids to start from 1001 – Illusionist Jul 01 '20 at 15:56

Find missing values within range of running counter

1 Answers1