Re: Searching for deleted/skipped numbers

: He'l not have much luck with these. : These are examples of sql-statements that return the correct results for : the example data, but not for the general case. (One example: What if two : consecutive values are missing?) : I know of no way using SQL alone to find unused integer values in a field. : The only way I know is a 4gl (or other procedural language) function that : selects the field with an order by and checks for unused values in the : result. : Of course you can do something similar directly with sql (perhaps using the : group by technique someone suggested), but it would be a lot of effort. Maybe you could try something like : select (t1.field + 1) as first, (t2.field - t1.field - 1) as count from table t1, table t2 where t2.field = (select min(t3.field) from table t3 where t3.field > t1.field) and (t2.field - t1.field) > 1 This should give every first missing value and the number of missing values of all groups of consecutive missing values of the field 'field' in table 'table'. Regards, -- ----------------------------------------------------------- Bart Van der Cruyssen Bart.VanderCruyssen@rug.ac.be UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM tel: +32/9/264.55.10 fax: +32/9/264.58.42