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 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.

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/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.

• sqlite

• 1 This question is similar to: Get minimum unused value in MySQL column. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – jhnc Commented Mar 11 at 4:56

3 Answers 3

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.

Here's a solution that'll work reasonably well, for small enough tables:

import sqlite3
import os

# setup a simple test table
if os.path.isfile('test.db'):
    os.remove('test.db')
conn = sqlite3.connect('test.db')
conn.execute('''CREATE TABLE TEST (x INT);''')
for x in [1, 2, 3, 5, 6, 8, 9]:
    conn.execute(f"INSERT INTO TEST (x) VALUES ({x});")
connmit()

# this is the query you may want
query = """
SELECT COALESCE(
    (SELECT MIN(t1.x + 1)
     FROM test t1
     LEFT JOIN test t2 ON t1.x + 1 = t2.x
     WHERE t2.x IS NULL), 
    0) AS lowest_unused
"""

# show the result
for row in conn.execute(query):
    print(row[0])

Output:

4

If you want to do this on tables with a large number of records, consider putting an index on the field that you're doing it for.

The existing answers already give working answers, but I wish to address specifically your first query, which is almost correct... The only issue is within your subquery, where the two `colname` in the where expression refer to the same column in the subquery's table. It is never true that a column is equal to itself + 1, so you will only ever get empty results.

You have to specify that one column is from the subquery's table, and the other from the outer query's table using table aliases:

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

The T1 alias is not actually necessary, but I find it adds clarity to the where clause.

For info, this type of subquery is called a correlated subquery.