Re: Finding missing integers

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 > If you are missing a relatively small number of integers, and want to start of by getting a quick sense of how many and where they are, here's something that you might try. Let's say I have a table "extract" that contains a serial column and I suspect that a few records have since been deleted. I run the following: select min(ext_id), max(ext_id), count(*) from extract ; which returns this: (min) (max) (count(*)) 1 192000 191991 This confirms that I am missing a few, 192000 - 191991 = 9 in fact. To get a sense of where the missing numbers are, I run the following: select ext_id - mod(ext_id, 10000), count(*) from extract group by 1 order by 1 which returns this: (expression) (count(*)) 0 9999 10000 10000 20000 10000 30000 10000 40000 10000 50000 10000 60000 10000 70000 10000 80000 10000 90000 10000 100000 10000 110000 10000 120000 10000 130000 9992 140000 10000 150000 10000 160000 10000 170000 9999 180000 10000 190000 2001 I can see right away that 8 of the missingf integers lie in the range 130000 - 139999, and that there is another missing one in the range 170000 - 179999. If I am dying of curiosity about those 8, I can modify my query to read: select ext_id - mod(ext_id, 100), count(*) { changed 10000 to 100 } from extract WHERE ext id BETWEEN 130000 and 139999 group by 1 order by 1 and this returns: expression count 130000 100 130100 100 [...] 131100 100 131200 92 131300 100 [...] 139900 100 That is, every row says "100" except for 131200 which says "92". So now I know that my missing 8 lie in the range 131200 to 131299. Note that "ext_id - mod(ext_id, 10000)" means, in effect, "round this integer down the nearest multiple of 10,000". Hope this helps. Paul (not a spokesman, etc.)