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
解决方案
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.