Re: Searching for deleted/skipped numbers

Joey Duhon writes: * * >> query an integer field for skips in a range? e.g. if I search a field of * >> integers ranging from 1 to 100 can I find the unused numbers? * * you can find the gaps: * * select a.intcol, max(b.intcol) gap * from table a, table b * where a.intcol between 1+1 and 100 * and a.intcol > b.intcol * group by 1 * having max(b.intcol) < (a.intcol-1) * * you can check the lower end by simply finding the min(intcol) Here is a different version of the same query returning the before, missing, and after values of missing values. All you need is the "missing". This, also, does not return the values if, say in a 1 to 100 record table, the value was 1 or 100. Also, make sure there is an index on the intcol field. select a.intcol after, (a.intcol - 1) missing, max(b.intcol) before from table a, table b where a.intcol > b.intcol group by 1 having max(b.intcol) < (a.intcol - 1); Robert Minter Data Systems Support \\\\\\_/// Senior Software Engineer A Client Technologies Company ( _ _ ) E-Mail: rob@dssmktg.com Tel: 714.771.0454 (| ^ |) #include <disclaimer.h> Fax: 714.771.3028 \\`-'/ De Colores - Emmaus OC-13 SURF'S UP \\_/