Re: Finding missing integers

Posted in 1996

Bashar Chalabi — Wed, 28 Feb 1996 20:26:30 GMT — source: Informix-list mailing list archive (1991-1998)

> arcieri@pilot.njin.net (Joseph Arcieri) writes: > Hi, I'm not sure how to do this. I'm running SQL v4/SE v5 and have several > databases that use an integer field as the record id. Is there a way to > find skipped or unused integers in the field? e.g. Each row that's added > has a unique number added automatically, but when rows are deleted, those > numbers are available to be used again. How can I search for used numbers/integers in a range? Make senes?possible? Thanks, Joe Arcieri > > >>>> Select min(RecordID) + 1 from TableX where RecordID not in (Select t1.RecordID from TableX t1, TableX t2 where t1.RecordID = t2.RecordID - 1) should (I have not tried it) return the smallest unsed number. I hope your hardware is up to it. You will probably be better off writing a stored procedure that loops until : CurrentRecordID <> PreviousRecordID +1 Bashar Chalabi Card Tech Limited, London