Re: Query to find first missing integer in a field

[...]
>
> 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)
from

   (select

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

   diff>1;

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

HTH,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |