Re: Query to find first missing integer in a field

From: Michael Austin <>
Date: Tue, 07 Apr 2009 15:40:10 -0500
Message-ID: <PSOCl.15777$>

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

not to mention any referential integrity that may get munged because it was designed to have missing numbers. IMO there are VERY few reasons to EVER have non-gap sequential numbers. Even in accounting you can VOID a check number... take the hint. Received on Tue Apr 07 2009 - 15:40:10 CDT

Original text of this message