Re: Finding missing integers

Posted in 1996

Paul Roberts — 23 Feb 1996 21:17:32 GMT — source: Informix-list mailing list archive (1991-1998)

In article <4g235t$4c6@pilot.njin.net>, Joseph Arcieri <arcieri@pilot.njin.net> wrote: >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 Paul Roberts <proberts@lynx.informix.com> suggests: > select ext_id - mod(ext_id, 10000), count(*) > from extract >group by 1 >order by 1 I ought add that this will only work if the "mod" function is available in your version of the software - I am not sure when it was introduced. I'm using I-SQL 6.01 to run the examples I gave in my previous posting. Paul (still not a spokesman)