Re: Query to find first missing integer in a field

From: DeanB <>
Date: Tue, 7 Apr 2009 13:38:21 -0700 (PDT)
Message-ID: <>

On Apr 7, 4:08 pm, ddf <> wrote:
> On Apr 7, 2:51 pm, dean <> wrote:
> > 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.
> This has 'DON'T DO THIS' written all over it in large, bold, red
> letters as it's a disaster in the making.  I suppose you're looking to
> 'backfill' missing sequence numbers and that is a recipe for failure.
> Seriously rethink this before you're in too deep and end up with an
> application that won't run because it won't scale.
> David Fitzjarrell

Correct - the problem is that our consultants tend to poke around in the database and analyse data, and its far easier to do this if the ID numbers are 1234 rather than 158943758.

Also, sequences are not reproducable, so debugging a series of SQL statements is sometimes a difficult process. Received on Tue Apr 07 2009 - 15:38:21 CDT

Original text of this message