4th Floor, 4 Tabernacle Street London EC2A 4LU

Select the next available value in a table

Let’s say I have a table like this:

ID Value
———————-
100 val1
101 val2
104 val3
105 val4
107 val5

[sql] SELECT Min(r1.uid) + 1 AS next_available_uid
FROM unixuid AS r1
LEFT OUTER JOIN unixuid AS r2
ON r2.uid = r1.uid + 1
WHERE r1.uid > 200
AND r2.uid IS NULL

[/sql]