braindamage braindamage - 7 months ago 17
SQL Question

mysql find smallest + unique id available

i have a column ID and something like 1000 items, some of then were removed like

id=90, id=127, id=326


how can i make a query to look for those available ids, so i can reuse then for another item?

its like a
min(ID)
but i want to find only the ids that are NOT in my database, so if i remove a item with the
ID = 90
, next time i click on ADD ITEM i would insert it as
id = 90

Answer

You can get the minimum available ID using this query:

SELECT MIN(t1.ID + 1) AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL

What it does is that it joins the table with itself and checks whether the min+1 ID is null or not. If it's null, then that ID is available. Suppose you have the table where ID are:
1
2
5
6

Then, this query will give you result as 3 which is what you want.

Comments