finding a missing data in a table

davidklonski

Hello

I have a table which a column holding UserIds of type bigint(5)

Usually I will have consecutive UserIds in the table, i.e.: 1,2, 3,4,...
However, it is possible that the table will have whole holes in the data: 1,2,5,6, 8,...
Is it possible to detect such holes using SQL?
for example, if the data is: 1,2,5,6,8
then I would like to get the lowest missing id. In the above example it would be 3

thanks in advance

mrhappiness

SELECT (tab1.id + 1) missing
FROM
  yourtable tab1
  LEFT JOIN yourtable tab2 ON tab1.id + 1 = tab2.id
WHERE
  tab2.id IS NULL
ORDER BY 
  missing ASC
LIMIT 1

should work but why do you want to do that?

steadyguy

I posted a solution to a similar problem here: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10273510

Btw ... as BIGINT has considerably more than 5 digits it would be more elegant, and you could probably save yourself a bit of diskspace, by using MEDIUMINT ... Come to think of it, even MEDIUMINT is overkill (especially if UNSIGNED), but if you think you might eventually go higher than 65535 (the max of a SMALLINT UNSIGNED column; yes, I had to look that number up 😃 ), then it's your next best choice.

davidklonski

thanks
The code was almost perfect, I had to change the selected column from:

SELECT (tab1.id+1) missing
FROM
yourtable tab1
LEFT JOIN yourtable tab2 ON tab1.id + 1 = tab2.id
WHERE
tab2.id IS NULL
ORDER BY
missing ASC
LIMIT 1

to:

SELECT (tab1.id) missing
FROM
yourtable tab1
LEFT JOIN yourtable tab2 ON tab1.id + 1 = tab2.id
WHERE
tab2.id IS NULL
ORDER BY
missing ASC
LIMIT 1

I am using this code to allocate user id for people.

Since people can be deleted from my systems, I would like to recycle used ids...