Get minimum unused value in MySQL column

Question

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?

Barmar · Accepted Answer · 2014-09-08T08:19:18.753

12

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

edited Sep 08 '14 at 08:19

answered Sep 08 '14 at 08:12

Barmar

669,327
51
454
560

Works like a charm! Thanks a ton @Barmar! – Giorgio Sep 08 '14 at 08:24
@Barmar And how do I include other where conditions in this query? Fo example if I want to select a number from a collection of rows with SubId: 10? – Moe Epo Jul 30 '18 at 23:38
How does that other condition fit in with finding holes in the ID sequence? If ID = 10 fits the condition and ID = 11 doesn't fit the condition, should it return `11`? – Barmar Jul 31 '18 at 06:48
@Barmar Good I checked this answer again after linking it to my new [question](https://stackoverflow.com/questions/51611113/find-minimum-non-used-value-from-collection-of-rows-marked-with-an-id-and-custom) or I would not have seen your reply without the @... No, I mean I want the query to work with rows which has specific customId as well. So, for ex., The table could contain rows from 1 to 10 with 2 missing for every customId, let's say it is 1 and 2. In total this would be 20 rows, however I need to select only from a select of the customId 1. – Moe Epo Jul 31 '18 at 10:39
So the query would work only with ten rows in this example, and give me the missing value from this, which is what I want, as opposed to scanning the whole table. – Moe Epo Jul 31 '18 at 10:39

score 4 · Answer 2 · answered Sep 09 '14 at 09:29

4

A slightly different way to do it using a join rather than EXISTS:-

SELECT MIN(t1.id)
FROM 
(
    SELECT 1 AS id
    UNION ALL
    SELECT id + 1
    FROM yourTable
) t1
LEFT OUTER JOIN yourTable t2
ON t1.id = t2.id
WHERE t2.id IS NULL;

Down side of any solution using a sub query is that they are not likely to use any indexes

answered Sep 09 '14 at 09:29

Kickstart

21,251
2
19
33

Wow, simple and effective! Thanks very much and +1 for the help!! – Giorgio Sep 09 '14 at 11:50

score 1 · Answer 3 · answered Sep 08 '14 at 08:05

1

You can create a table with just numbers in it. I'm simulating this table in below query. Then you can left join this table.

SELECT
MIN(numbers.n) AS missing_value
FROM (SELECT 1 as n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers
LEFT JOIN your_table yt ON numbers.n = yt.id
WHERE yt.id IS NULL

answered Sep 08 '14 at 08:05

fancyPants

49,071
32
84
94

This solution may work but forces me to create a table with all numbers... from 0 to infinite... am I right? – Giorgio Sep 08 '14 at 08:17
The sub query does that and if the range is reasonable it is easy to expand the sub query to provide a large range (you use a couple of sub queries cross joined together, each getting the digits 0 to 9, and then use one as units, one as tens, one as hundreds, etc) – Kickstart Sep 09 '14 at 09:12

score -1 · Answer 4 · answered Sep 08 '14 at 08:07

-1

If you have values from 1 to n in some other table say t2 then by simply checking

select min(id1) from t2 where id1 not exist(select id from t1);

you will get your answer;

answered Sep 08 '14 at 08:07

Vishal

471
6
14

This isn't even legal mysql – Roemer Apr 03 '20 at 19:35

Get minimum unused value in MySQL column

4 Answers4

Linked