Re: Query to find first missing integer in a field
Date: Wed, 08 Apr 2009 23:17:33 +0200
> Does anyone know a good way to find the first free integer number that
> does not exist in a table in a specified column? I can think of a
> couple of tedious ways of doing this but they are hardly elegant.
> Assume there could be any integer present up to 2 billion.
Not exactly a good way.
Have an index on that column, column_x.
select column_x + 1
from table_x tx
where column_x > 0
and not exists ( select null from table_x txc where txc.column_x = tx.column_x + 1 ) and rownum <= 1;
If I was sure no row would ever be deleted, I'd store that number and start from that one the next time. Received on Wed Apr 08 2009 - 16:17:33 CDT