[SOLVED] SQLite Find missing numbers between range (3000-4000)

SQLite Find missing numbers between range (3000-4000)

I'm looking to find the lowest missing number between 2 numbers.

there may be jumps between 3080 and 3085, etc and all I would need is 3081. This is for assigning an id to an entry and these entries can be deleted thus the id is too and because we have a limited range we want to make sure we use all the numbers. So if there aren't any missing numbers it would have to grab the next available number. if it's full till 3850 then it would need to grab 3851.

They are 0 if they have been deleted rather than null if that helps.

SELECT number, user_id
FROM entries
WHERE user_id = '18'
AND number BETWEEN '3000' AND '4000'

I'm not sure how to get the lowest available number in this sequence, please help. Thank you.

Solution

Try using a self join:

SELECT MIN(e1.number) + 1 num_missing
FROM entries e1
LEFT JOIN entries e2
    ON e2.number = e1.number + 1
WHERE e1.id = 18 AND e1.number BETWEEN 3000 AND 4000 AND
    e2.number IS NULL;

The critical condition in the WHERE clause is e2.number IS NULL, which means that this particular number had no next immediate value in the sequence, i.e. it is the start of a gap.