Re: Query to find first missing integer in a field

From: Lothar Armbrüster <>
Date: Wed, 08 Apr 2009 17:51:53 +0200
Message-ID: <87vdpfuorq.fsf_at_prometeus.nothing.none>

Mark D Powell <> writes:

> Here is one way to find the missing keys
> --
> -- select complete_range_of_interest (query to generate 1..N - many
> examples on web)
> -- from source_with_enough_rows
> -- where range >= start_of_interest and range <=
> max_value_of_interest (filter for min and max values in table)
> -- minus
> -- select values from table_of_interest
> --
> Be advised if you plan to update existing key to lower skipped values
> that you have two issues that you may have to deal with: 1- if there
> are FK relationships to the value you have to update the child
> tables. If the FK relationship is not defined in the database but
> only in the application you may easily miss a necessary update, and 2
> - if you have any purge and archive processes then by re-using a key
> you make restoration of archive data either impossible or create false
> relationship between rows of data by restoring the reused key.
> HTH -- Mark D Powell --

Here is another way that does not involve a second table. It makes smart use of analytic functions:

select min(next_id)


       id+1 next_id,
       lead(id,1) over (order by id)-id diff



Be aware that this does a full table scan or a full index scan.


Lothar Armbrüster  |
Hauptstr. 26       |
65346 Eltville     |
Received on Wed Apr 08 2009 - 10:51:53 CDT

Original text of this message