00 Votes

MySQL: Get next free number after a given number

Question by PC Control | 2012-05-01 at 15:47

In MySQL, I want to find the closest possible free number, that has not yet been assigned, to a given number. To demonstrate what I want, here is an example:

1  
2
3   --> the next would be 4
10
11  --> the next would be 12
20
21
22  --> the next would be 23

For instance, the next closest possible number to 1 would be 4, if I am searching for the next free number after 10, the result would be 12 and for 20, the result should be 23, according to this example.

Is there a way to realize this in MySQL?

ReplyPositiveNegative
Avatar Stefan Trost
1Best Answer1 Vote

The following query should work. You still have to replace "tab" with the name of your table and "id" with the name of the column, in which the values are.

SELECT MIN(tab.id + 1)
FROM tab
LEFT JOIN tab AS temptab
ON tab.id + 1 = temptab.id
WHERE temptab.id IS NULL AND tab.id >= 10

In the last line, you have to replace the rear "10" by your comparison value. Applied to your example above, this query should output the value 12.
2012-05-03 at 17:45

ReplyPositive Negative
Reply

Related Topics

Windows Batch Script: Computer Shutdown

Tutorial | 2 Comments

MySQL: Change minimum word length for full text search

Tip | 1 Comment

MySQL: Regular Expressions in MySQL queries

Info | 0 Comments

MySQL: CSV Export as File stored on the Server

Tutorial | 0 Comments

MySQL: CSV Export as automatic Download

Tutorial | 0 Comments

MySQL/PHP: How to retrieve the last INSERT ID

Info | 0 Comments

MySQL: How many records have been found or are affected by my query?

Info | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.