Re: Query to find first missing integer in a field

From: Gerard H. Pille <>
Date: Wed, 08 Apr 2009 23:17:33 +0200
Message-ID: <49dd146a$0$2861$>

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

Original text of this message