Re: Query to find first missing integer in a field
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Wed, 08 Apr 2009 23:17:33 +0200
Message-ID: <49dd146a$0$2861$ba620e4c_at_news.skynet.be>
dean schreef:
> 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.
Date: Wed, 08 Apr 2009 23:17:33 +0200
Message-ID: <49dd146a$0$2861$ba620e4c_at_news.skynet.be>
dean schreef:
> 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