Re: How to create a solid sequence?

[Gary Golden]

The data has dynamic nature, it WILL be deleted and/or modified.

I have an idea, may be we can develop it together.
What if we create temporary table with values range, then select difference like this:

SELECT `range`.id
FROM test
RIGHT OUTER JOIN (
SELECT 1 AS id
UNION SELECT 2 AS id
UNION SELECT 3 AS id
...
UNION SELECT 255 AS id
) AS `range` USING(id)
WHERE test.id IS NULL
ORDER BY `range`.id
LIMIT 1;

This way we can find first free value and use it in insert.
This is quite ugly for now.
Do you have any idea how to simplify?



Edited 1 time(s). Last edit at 06/24/2011 10:28PM by Gary Golden.