Re: Searching for deleted/skipped numbers

: Chris Jones <ddsiecj@sunny.oed.db.za> writes: : G'Day Joe. : What you could try is the following.. : : eg. Sample table structure table_1 (field1 integer). : Sample data (1, 2, 4, 6, 7). : : SELECT a.field1 - 1 : FROM table_1 a, : table_1 b : WHERE a.field1 - 1 <> b.field1 : : Or : : SELECT a.field1 - 1 : FROM table_1 a : WHERE a.field1 - 1 IN ( SELECT field1 : FROM table_1) : : Should return ( 3, 5 ) : : Give these a try...Good Luck. 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. But why bother? That's the unanswered question to me. Nils.Myklebust@ccmail.telemax.no NM-data, Aasesvei 71, 1300 Sandvika, Norway My opinions are those of my company