I have a table with integer ID column. I would like to get the minimum unused value for this column. The query should find the first hole in table IDs and get the minimum value inside it. I’ll try to explain it with some examples.
Example 1: no-holes table
In this case, I have a table without holes and query should simply get the minimum unused value: should get: 4
|id| |1 | |2 | |3 |
Example 2: table with hole on top
In this case, we have a hole on top (missing value: 1). The query finds the hole and gets the minimum value inside it: should get 1.
|id| |2 | |3 | |4 |
Also in this case, we have a hole on top, but we have more missing values inside it (missing values: 1 and 2). The query finds the hole and gets the minimum value inside it: should get 1.
|id| |3 | |4 | |5 |
Example 3: table with hole in the middle
In this case, we have a hole in the middle (missing values: 2 and 3). The query finds the hole and gets the minimum value inside it: should get 2.
|id| |1 | |4 | |5 |
Example 4: table with holes on top and in the middle
In this case, we have multiple holes: one on top (missing value: 1) and one in the middle (missing value: 3). The query finds the first hole and gets the minimum value inside it: should get 1.
|id| |2 | |4 | |6 |
I’ve tried the solution proposed in this post, but it doesn’t work as expected in my case. Any ideas?
Advertisement
Answer
SELECT min(unused) AS unused FROM ( SELECT MIN(t1.id)+1 as unused FROM yourTable AS t1 WHERE NOT EXISTS (SELECT * FROM yourTable AS t2 WHERE t2.id = t1.id+1) UNION -- Special case for missing the first row SELECT 1 FROM DUAL WHERE NOT EXISTS (SELECT * FROM yourTable WHERE id = 1) ) AS subquery