[SOLVED] Find lowest unused integer in column

Find lowest unused integer in column

I have a SQLite database table with an integer type column. In it I have values like 1, 2, 3, 5, 6, 23.

Starting at 1, I'd like to find the lowest unused integer? In this example it would be 4.

My problem is like SQL - Find the lowest unused number except I'm using one table, and a potential further complication in that I might have less functions available in SQLite.

I'm trying to use the first solution's answer https://stackoverflow.com/a/39817314/2081511 but nothing is returning. I'm not sure if it's because of the differences mentioned above.

SELECT 
    MIN(`colname` + 1) 
FROM 
    `mytable` 
WHERE 
    NOT EXISTS (SELECT 1 
                FROM `mytable` 
                WHERE `colname` = `colname` + 1);

I've tried breaking it down to just the subquery

SELECT 1 
FROM `mytable` 
WHERE `colname` = `colname` + 1

but I'm not getting any results.

Solution

Well it depends on the size your table / data, but you could do something like this

With the NOT IN your are checking if the column + 1 is in the Table. This select would return all missing Numbers and with the Aggregate function MIN you select the smallest.

SELECT MIN(ProductID  + 1 ) AS HELPER_ID 
FROM Product 
WHERE (ProductID + 1) NOT IN (SELECT ProductID FROM Product)

Here a working Demo on sqlfiddle

Depending on the size of the table the IN "function" cause performance issues.